HELP!!!! --> pulling from two tables with one COUNT 1

[cyclops1eye]

HI! I have a fun one here. I am trying to populate a ListBox in Access using two tables... and what I have for fields are these:

ID Product_Number Serial_Number Checked
-- ------------------- ----------------- ----------

Now, the "Checked" field doesn't go into the ListBox, it's used as a field for criteria to get into the ListBox.

Assuming that both tables are set up equally, what I need to see in the ListBox is a distinct entry for each product / serial number combination FROM Table1 WHERE Checked=True... The trick is this: Each line in the listbox needs to be distinct, I also need to have the ID in the ListBox from Table1 only -- (although I don't want it visible), AND I need to have another field in the ListBox that COUNTs all the times that each Serial_Number that shows up in the ListBox exists in Table1 and Table2 regardless of whether or not Checked=True.

What I have is this:


"SELECT ID, A1.Product_Number AS PN, A1.Serial_Number AS SN, COUNT(A1.Serial_Number) + COUNT(A2.Serial_Number) AS COUNT

FROM Table1 AS A1, Table2 AS A2

WHERE A1.Checked=True

GROUP BY A1.Product_Number, A1.Serial_Number"


The Problem with this is that it yields only a count of 1, because even though the Serial_Number may appear several times within the database, it is only going to be checked once. What I need to show up in the COUNT column is not the count of the ID / Product_Number / Serial_Number combination, but rather a count of all of the Serial_Number's that appear within the database at all.

Does that make sense?

PLZ HELP!


--
Cyclops_One_Eye

 

[rac2]

Assuming that both tables are set up equally". Do you mean that the two tables have the same columns? The same rows? How are the tables used?

 

[cyclops1eye]

I was meaning same columns. That is actually not the case, but I am familiar enough with SQL that I was trying to make it easier for someone to answer my question...

What is your question about how the tables are used?

--
Cyclops_One_Eye

 

[PHV]

Something like this ?
"SELECT ID, Product_Number AS PN, Serial_Number AS SN
, ((SELECT COUNT(*) FROM Table1 AS A1 WHERE A1.Serial_Number=C.Serial_Number)
+ (SELECT COUNT(*) FROM Table2 AS A2 WHERE A2.Serial_Number=C.Serial_Number)) AS CountOfSN
FROM Table1 AS C
WHERE A1.Checked=True"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cyclops1eye]

That looks just like what I need... just wasn't sure how to express it in SQL... Thank you very much! I will have to go try it out now...

I will let you know what I encounter.
Cheers,

-one_eye

 

[cyclops1eye]

DUDE!!! THIS WORKS GREAT!!! THANKS A MILLION!!!....

One modification that I did make, though, was to change COUNT(*) to COUNT(1), so that I didn't have it loading all fields...

Anyway, thank you very much!!!!

-one_eye