How to count equivalent items from different fields

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

Guest

Hi
I have a table describing cables and their connectors on both ends.
The cables may have same or different type of connectors on the ends; let’s
say male-male or male-female.
The fields of the table “Cablesâ€are formed as “CableIDâ€, “CableTypeâ€,
“EndAConTypeâ€, “EndAConQtyâ€, “EndBConnTypeâ€, “EndBConQtyâ€.
I have tried to make a query to give the quantity sum of the same type of
connectors either if they are in EndA… or in EndB… fields, but I haven’t
found any working solution. Do you have any suggestion?

Thanks in advance
GL
 
Hi
I have a table describing cables and their connectors on both ends.
The cables may have same or different type of connectors on the ends; let’s
say male-male or male-female.
The fields of the table “Cables”are formed as “CableID”, “CableType”,
“EndAConType”, “EndAConQty”, “EndBConnType”, “EndBConQty”.
I have tried to make a query to give the quantity sum of the same type of
connectors either if they are in EndA… or in EndB… fields, but I haven’t
found any working solution. Do you have any suggestion?
Click to expand...

Try a UNION query:

SELECT Cables.CableID, Cables.EndAConType AS ConType
UNION ALL
SELECT Cables.CableID, Cables.EndBConType AS ConType

This will take a 1000 row table and give you a 2000 row recordset,
with each cable listed twice, once for each end. You can then do
totals queries as needed, based on this query.

John W. Vinson[MVP]
 
Back
Top