Require at least two records before displaying results

[akporta]

I’m working on a database for a sludge disposal company. They take a load of sludge (which is the ultimate record) at a plant and apply it to a zone within a site somewhere in the state (think fertilizer). One plant can send loads to many zones, and a zone might receive loads from many plants. My reports need to summarize the loads. One report I have shows all the loads from a given plant to all the zones that plant applied loads to. That one is simple enough.

My trouble starts with the report that needs to show, for a given plant, the zones that it SHARED with other plants. So the report can never show less than two records, and for each zone one of the records must be for that given plant. I’m trying to figure out how to set the criteria in my report to require it to show only zones that have more than one record, and one of them must be that given plant.

The relevant fields are Load ID, Zone (the zone this load went to), and Facility (where the load came from). Other fields are the chemicals on the load, addresses, etc.

Any pointers?

[brju]

Based on what you have included in your post (limited info on tables and relationships), I suspect this is more of a design issue. How have you set-up the tables?

[akporta]

I had a feeling I wasn't providing enough information. Let me try to demonstrate:

My tables (simplified):
tblZones
ZoneID
ZoneSize

tblFacility
FacilityID
FacilityAddress

tblLoad
LoadID
LoadQty
LoadZone (ZoneID from tblZones)
LoadFacility (FacilityID from tblFacility)

The Load is actually the "main" record by which everything is summarized (i.e., Load 1 had a quantity of 1, came from facility FLA2289 and applied to Zone 5A, etc.). I have a query for that:

qryLoad
LoadID LoadQty LoadFacility LoadZone
1........1.........FLA2289.....5A
2........1.........FLA3206.....5A
3........2.........FLA2289.....6A
4........1.........FLA1175.....7C

I have a user-input form to ask for which facility he would like a report. Let's assume here FLA2289. The report query would look like this:

LoadID LoadQty LoadFacility LoadZone
1........1.........FLA2289.....5A
2........1.........FLA3206.....5A

Because:
1. Facility FLA2289 must be a Facility for that Zone in order for it to be displayed (then any other Facility that also went to that Zone needs to appear);
2. A Zone must have at least two records (notice that Load 3 came from Facility FLA2289, but didn't get included because Zone 6A only had one record; that is, no other Facility used that Zone.).

Any ideas of how to build an expression and/or criteria on the report query to require those two parameters? I know nothing about SQL (I consider myself a beginner), but it almost feels like the ZoneID field in the report query should have some sort of validation rule (at least two records, and one of them must be the Facility from the user-input form).

[GolferGuy]

I know there might be a better way, but at least this works. I did it in 4 queries. First query selects ONLY the requested LoadFacility "FLA2289". The next selects all other LoadFacility(s) <>"FLA2289". The third query selects all the records from the table with the requested LoadFacility that has a matching LoadZone in the second query. The last query, returns all the records from the second and third queries.

Here is the SQL for the 4 queries, with the names I gave each query.
First: qFLA2289
SELECT LoadID, LoadQty, LoadFacility, LoadZone
FROM tblLoad AS A
WHERE LoadFacility="FLA2289";

Second: qFLA2289Not
SELECT B.LoadID, B.LoadQty, B.LoadFacility, B.LoadZone
FROM tblLoad AS B INNER JOIN qFLA2289 ON B.LoadZone = qFLA2289.LoadZone
WHERE (((B.LoadFacility)<>"FLA2289"));

Third: qFLA2289Primary
SELECT C.LoadID, C.LoadQty, C.LoadFacility, C.LoadZone
FROM qFLA2289 AS C INNER JOIN QFLA2289Not ON C.LoadZone = QFLA2289Not.LoadZone;

Fourth: qLoadFacilityForReport
SELECT qFLA2289Primary.LoadID, qFLA2289Primary.LoadQty, qFLA2289Primary.LoadFacility, qFLA2289Primary.LoadZone
FROM qFLA2289Primary;
UNION
SELECT QFLA2289Not.LoadID, QFLA2289Not.LoadQty, QFLA2289Not.LoadFacility, QFLA2289Not.LoadZone
FROM QFLA2289Not;

Hope this helps,
Vic

[akporta]

All I care is that it works. Let me see if I understand. I will need to create, and have Access run, four different queries consecutively in order to get the results? My current query is actually the SQL statement on the report Data Source. Since I'm not used to SQL I just built it with the query designer instead. Based on this, how do I get Acess to run all four queries at the time my user presses the OK button on my form? He will know nothing of Acess and can only expect a report to pop up.

Thanks.

[GolferGuy]

Have you tried to run the final query? If so, you would notice that it uses the 3rd query, which uses the 2nd query, which uses the 1st query. When one query uses the output of another query, that causes the referenced query to run. You need do nothing except make the last query the record source for the report. Then when you open the report, that causes the query to run, which causes the 3rd one to run, etc.