Too many records

E

EB

I have two queries, A & B. Query A matches appropriately sized hardware from Table A with upper components from Table B. Similarly, Query B matches appropriately sized hardware from Table A with lower components from Table B. The selection criteria is such that either one or two hardware items are selected for each component
I would like to include both upper and lower hardware on a report for purchasing. I created Query C to join Queries A & B. Unfortunately, this queries returns every possible combination of upper and lower hardware items, rather than the one or two upper items and the one or two lower items
Example: Component A upper requires hardware 152 and 153. Component A lower requires hardware 152 and 153, also. (this is not always the case; lower could be 131, only
Query C returns 4 records: U 152 L 152, U 152 L 153, U 153 L 152, U 153 L 153.
I would like to see 2 records: U 152 L 152, U 153 L 153.
Any help is appreciated
EB
 
J

Jeff Boyce

Consider looking into a UNION query. This takes the results of query1 and
query2 and displays them all. You'll have to copy the SQL statements of
each into a new, third, SQL-only query.

Good luck

Jeff Boyce
<Access MVP>
 
J

Jeff Boyce

Perhaps you and I have a different notion of what would be in the three
queries. How 'bout if you post the SQL statements of Q1, Q2, and the UNION
Q?
 
E

EB

Jeff, here are the SQL statements
Q A:SELECT Balances.[Balance #], Balances.[Glass Height], Balances.[Min Weight], Balances.[Max Weight], DH.TSWGHT1, DH.TYPE, DH.OATVDLO, IIf(Int(DH!OATVDLO) Mod 2=0,Int(DH!OATVDLO),Int(DH!OATVDLO)-1) AS [GH Select], DH!QTY1*DH!QTY AS QT
FROM Balances, D
WHERE (((DH.TSWGHT1) Between [Min Weight] And [Max Weight]) And ((IIf(Int(DH!OATVDLO) Mod 2=0,Int(DH!OATVDLO),Int(DH!OATVDLO)-1))=[Glass Height])
ORDER BY DH.TYPE
Q B:SELECT Balances.[Balance #], Balances.[Glass Height], Balances.[Min Weight], Balances.[Max Weight], DH.BSWGHT1, DH.TYPE, DH.OABVDLO, IIf(Int(DH!OABVDLO) Mod 2=0,Int(DH!OABVDLO),Int(DH!OABVDLO)-1) AS [GH Select], DH!QTY1*DH!QTY AS QT
FROM Balances, D
WHERE (((DH.BSWGHT1) Between [Min Weight] And [Max Weight]) And ((IIf(Int(DH!OABVDLO) Mod 2=0,Int(DH!OABVDLO),Int(DH!OABVDLO)-1))=[Glass Height])
ORDER BY DH.TYPE
Q C:SELECT Balances.[Balance #], Balances.[Glass Height], Balances.[Min Weight], Balances.[Max Weight], DH.TSWGHT1, DH.TYPE, DH.OATVDLO, IIf(Int(DH!OATVDLO) Mod 2=0,Int(DH!OATVDLO),Int(DH!OATVDLO)-1) AS [GH Select], DH!QTY1*DH!QTY AS QT
FROM Balances, D
WHERE (((DH.TSWGHT1) Between [Min Weight] And [Max Weight]) And ((IIf(Int(DH!OATVDLO) Mod 2=0,Int(DH!OATVDLO),Int(DH!OATVDLO)-1))=[Glass Height])
UNION SELECT Balances.[Balance #], Balances.[Glass Height], Balances.[Min Weight], Balances.[Max Weight], DH.BSWGHT1, DH.TYPE, DH.OABVDLO, IIf(Int(DH!OABVDLO) Mod 2=0,Int(DH!OABVDLO),Int(DH!OABVDLO)-1) AS [GH Select], DH!QTY1*DH!QTY AS QT
FROM Balances, D
WHERE (((DH.BSWGHT1) Between [Min Weight] And [Max Weight]) And ((IIf(Int(DH!OABVDLO) Mod 2=0,Int(DH!OABVDLO),Int(DH!OABVDLO)-1))=[Glass Height])
ORDER BY DH.TYPE
Thanks for your help!
 
J

Jeff Boyce

No JOIN.

As I glanced over the QueryA statements, I didn't see a JOIN clause.

When you have two tables [Balances], [DH] in a query and don't join them,
you get a Cartesian Product (the # of rows selected in table1 times the
number of rows selected in table2).

Try rewriting the first two queries using JOIN clauses. Then build your
UNION.
 
Top