Too many records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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>
 
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?
 
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!
 
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.
 
Back
Top