Pulling Report from Access DB

[ribbons]

I have a perplexing problems I have worked on for days with no solution and I'm hoping someone out there has encountered this before. I am having problems pulling data from the DB for one type of report and THEN pulling data for another report, EXCLUDING THE DATA FROM THE FIRST REPORT

I am using CRXI with an Access DB and Windows XP. This is a windows project, a small DB with front ends to record and report data from Lab tests done on seed samples.

The database has several tables, three of which are:

LabNumber
LabelTest
GermReadings
Purity Readings

Entries and table joins go like this:
|----------------------------------------------|
| /---------------------------\ |
| / v v
LabNumber -------->Label Test------>GermReadings Purity

281009 Germination 57
Purity 95

I pull a report that reports Germination AND Purity readings for each company that submitted samples for germination and purity analysis (this one pulls data just fine).Then, I want to pull a SECOND report that will report Germination ONLY on samples submitted ONLY for germination (and not for purity).

I thought that I would be able to use the following record selection logic for the second report:

{LabelTest.TestName} <> "PURITY"

This should (I thought) pull all records for which there was no entry in the LabelTest Table that equaled "Purity". But for some reason that completely eludes me, this won't work. I have tried joining the tables in several different ways, with no luck. This seemed a very simple task, but it has proven not to be. Any help/ideas would be so appreciated. The programmer quit -- I'm the backup. Help. I apologize if this turns out to be a DB thing and this is posted in the wrong forum.

ribbons

 

[Madawc]

I doubt you can do it at the level of selection. What I'd do is select everything and then suppress the printing of some. This could be done for detail lines, but it might be easier to group using a formula field, e.g.

{LabelTest.TestName} = "PURITY" then "a) Purity"
else "b) Other"

This might even meet your need for two reports. Alternatively you can have a main report and a subreport with different rules in the report header or footer.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Regarding your links, you should not have two tables (label test and lab number) pointing to the same table germ readings.

To exclude samples that are submitted for both germination and purity, do not use a record selection on testname. Instead, create a formula in the field explorer {@purity}:

if {labeltest.testname} = "PURITY" then 1

Insert a group on a field like {labnumber.sampleno}. Then go to report->selection formula->GROUP and enter:

sum({@purity},{labnumber.sampleno}) = 0

-LB

 

[ribbons]

Thanks for your help with this.

I tried both of your solutions and this form is still pulling records for purity and germination. I did delete the link between the label test and the germ readings table. I brought these links over from the linkage in the Access DB, but breaking them does not seem to have affected the data either positively or negatively.

Could this lack of cooperation on the part of the form have something to do with the way the data is related in the database, overriding the way the data is related in the form?

 

[lbass]

I'm not really following you, but note that I only presented one solution. Using the group selection will result in a display of only those groups that do not have a purity record--but the records will still be in the report and would contribute to any inserted summaries. Take another look at the report. You might see groups in the group tree that do not appear in the report--these are the groups that are removed from the display. If you are calculating based on the displayed groups, you need to use running totals in order to exclude the non-displayed records (you don't have to do anything special to exclude them--the running total will only pick up on the displayed records).

-LB

 

[ribbons]

Hi lbass,

I don't have any running totals in this report. It only displays data drawn straight from the database. Let me see if I can clarify this in case I'm overlooking something really obvious. Given this:

Labnumber Table TestName Table

281004 Germination
Purity
TZ

282567 Germination

234567 Germination
Purity

Am I right in assuming that I can ask the form to give me a report on all the labnumbers from the Labnumber Table for which there is no entry in the TestName Table that equals "Purity"? Given that I am right, the above scenario would produce a report for ONLY labnumber 282567, right?

 

[lbass]

My suggestion would ONLY give your 282567. Isn't that what you want to happen?

-LB

 

[ribbons]

Yes, because that is the only entry of the three that has only germination in the field in the testname table IN THIS EXAMPLE. Right?

In the real world, the functionality I want this form to have is for it to pull all of the reports finished on a certain date that DO NOT have Purity in the field in the Testname Table. This seems like it should be so easy to do, I am completely stumped. I inherited this DB, so I need to get an idea whether or not I am formatting the form correctly as I suppose there could be some problems with the DB that I haven't discovered.

 

[lbass]

I am "stumped" as to why you don't think my suggestion is the solution.

-LB

 

[ribbons]

I've done a little more work with this and I see how I can get your formula to work, Lbass. The reports with Purity in the testname table DO NOT DISPLAY, but is there a way to get the same reports NOT TO PRINT, if I batch print? Like I said, there are no running totals.

ribbons

 

[lbass]

I don't know what you mean. If you print the report, they still will not show up. The running totals are not a necessary feature of this solution. I mentioned them only because they are necessary if you are also doing calculations across groups--since non-group-selected records contribute to inserted summaries, but NOT to running totals.

-LB

 

[ribbons]

LBass,

I printed these out and it works great! Thanks so much for your help. I would never have gotten that solution on my own.

ribbons