Count all LIKE items (1 Viewer)

[tt1611]

Hi All
This is a tricky one so please bare with me. The idea is I have two tables Master_List and PBO_List

The PBO table has two of several other columns namely pbo_asset_id and pbo_count
I use this table to run inventory on some bar codes. If a bar code is scanned, the pbo_count (of datatype yes/no - SQL Server bit) is set to yes (or -1). Column data in this table could be in the form

asset_id pbo_count
8001001 -1
8001002 -1
8001003 -1
8001004 -1
4001001 -1
4001002 -1
5001001 -1
5001002 -1
...

On the other hand, the parent table master_list has the parent asset ids in the form

Asset_id
8001
4001
5001
...

The master_list is a linked excel table. The idea here is to count asset_ids in the MASTER_LIST and their related total count in the PBO_LIST where pbo_count = yes (or -1).

so my query so far for example is
SELECT x.[asset id], x.[asset description], count(m.pbo_count) AS [Total Count]
FROM mastersheet AS x INNER JOIN PBO_LIST AS m ON instr(left(m.master_asset_id, len(x.[asset id])), x.[asset id])
WHERE m.pbo_count= -1
GROUP BY x.[asset id], x.[asset description];

My result set would be for example
8001 Table Set 4
4001 Chair Ser 2
5001 Driver Base 2

The query above (maybe complex) but worked to a certain extent but here are the problems

a) The returned result set was pulling in values where pbo_count was no (or 0)

b) some returned values were inconsistent with query so eg,
100 Teaching Lamps 5
would be returned because 100 existed in instr and the counts were all invalid (0) even after my condition WHERE m.pbo_count= -1

Please let me know if this makes sense at all or if you need further information.

I appreciate your help as always
Thank you

 

[jdraw]

Try

SELECT x.[asset id]
, x.[asset description]
, count(m.pbo_count) AS [Total Count]
FROM mastersheet AS x
INNER JOIN PBO_LIST AS m
ON left(m.master_asset_id, 4) = x.asset_Id
WHERE m.pbo_count= -1
GROUP BY x.[asset id]
, x.[asset description];

 

[tt1611]

Good thought Jdraw. Maybe I should have mentioned the point that the length of the mastersheet asset_id is variable and thus can be of length 4 - 9.
Any other suggestions on this are much appreciated.

 

[jdraw]

Yes, it would be a good idea to give all relevant info when posing a question.

Here's one for you. Why are the asset_ids structured differently in the 2 tables?

 

[MSAccessRookie]

Good thought Jdraw. Maybe I should have mentioned the point that the length of the mastersheet asset_id is variable and thus can be of length 4 - 9.
Any other suggestions on this are much appreciated.

Actually your original code was almost right. It just did not use the ON part of the JOIN correctly.

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana][B]SELECT [/B][/FONT][/COLOR][COLOR=black][FONT=Verdana][B]x.[asset id], [/B][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][B]   x.[asset description], [/B][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][B]   count(m.pbo_count) AS [Total Count][/B][/FONT][/COLOR]
[B][FONT=Verdana][COLOR=black]FROM mastersheet AS x INNER JOIN [/COLOR][/FONT][/B][COLOR=black][FONT=Verdana][B]PBO_LIST AS m [/B][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][B]   ON left(m.master_asset_id, len(x.[asset id])) = x.[asset id][/B][/FONT]
[B][FONT=Verdana]WHERE m.pbo_count= -1[/FONT][/B]
[B][FONT=Verdana]GROUP BY x.[asset id], x.[asset description];[/FONT][/B][/COLOR]
[/COLOR][/FONT]

Note that although you may not say that the Master ID can be variable in length, the original code allows for that possibility, so it should work in the proper context.