Combining like items within a Query

[AC5FF]

I'm working with an inventory control database. I have a query that runs just fine that will give me a running balance for each item in inventory. That query's output is just two columns; Stock Number and On Hand. (sorted by Stock Number in assending order...)

Now I need to further modify the output with (I would prefer) a 2nd query; but I don't know where to start for this one...

For example; Here's what my first query would return:

STOCK NUMBER	          ON Hand
4725-02-0019	          930
4725-02-0020-S2	          792
4725-02-0021	             5
4725-02-0021-S1	        3950
4725-02-0022	           22
4725-02-0022-S1	           15
4725-02-0022-S2	        1450
4725-02-0023	          912
4725-02-0024	           21
4725-02-0024-S1	        1850
4725-02-0025	        2683

But for a new report I am creating I need to condense this by prime Stock Numbers only. So; for example I would like the query to sum all the like Stock Numbers. Instead of showing 5 of 4725-02-0021 and 3950 of 4725-02-0021-S1 I want to be able to show 3955 of stock number 4725-02-0021. The same could be said of Stock Number 4725-02-0020-S2, I would like the output to show 792 of 4725-02-0020.

For reference - all stock numbers are in this same format of ####-##-#### with the following -S# being a substitute for the prime part.
In the case of the 4725-02-0020-S2 part, there is zero on hand for its prime part.

One other thing that just crossed my mind. If it helps, there is another table that lists every Stock Number - regardless of inventory. This table is where I can pull out the details of each stock number (part number/manufacturer/description/etc...)

Thanks!

 

[VilaRestal]

Not knowing the name of your query:

SELECT Left([STOCK NUMBER],12) AS [PRIMARY STOCK NUMBER], SUM([ON Hand]) AS [PRIMARY STOCK ON HAND]
FROM Query1
GROUP BY Left([STOCK NUMBER],12)

Change Query1 to whatever it should be (and field names too if need be)

 

[plog]

While Villa's response is the correct band-aid for your issue, what you really need to do is properly structure your database. Your stock number field should be broken out so that each discrete piece of data is in its own field. Mostly likely this means having 3 fields for the entire stock number.

The first field would hold '4725', the second would hold '02' and the third would hold this prime part. Once you do this you won't have to rely on string functions to carve up your data.

 

[AC5FF]

LEFT or RIGHT functions! Arrgghh! I knew I was forgetting something!

Thanks VilaRestal!! Your code worked like a champ!

 

[AC5FF]

Plog - while I understand what you are saying I sort of disagree - at least for my application here.

Each stock number is it's own unique item. Sure, all like items are grouped together - like in my example all 4725 parts are SMT resistors and all 4725-02 parts are THICK FILM CHIP SMT resistors. A -03 would be a SMT resistor array. But for me to link tables that have incoming/outgoing/moves/etc it is much easier to treat this as one 'text' field versus having to combine fields or link multiple fields together.

Just another point... although I use 4725-02-0020, I could just have easily started this as SMTR20 or SMTRA20 (SMT Resistor or SMT Resistor Array).

Just my 2cents worth...

 

[AC5FF]

VilaRestal:
I have now got a much bigger Right/Left/IF statement to write if you wanna help!
Personally I'm putting this one on the back burner for next week.

The query you helped to write links with a query that gives me the list of stock numbers required for any specified part. The problem is that "Parts List" was not standardized and can contain any of these options for the stock number field:
4725-02-0020
4725-02-0020-S2
[4725-02-0020]
[4725-02-0020-S2]

Note; no parts list would ever show the same stock number listed in different formats like this. However it could show 4725-02-0020 for Resistor 103, but also show [4725-02-0038] for [Resistor 103] (where the -0038 is an alternate to be used)

What I need to do is link the stock number from your query to the stock number in the "parts list" by using nested IFs and RIGHTs or LEFTs functions pull out just the main stock number, regardless of how it is listed in the 'parts list'

Oh what fun Monday holds!

 

[VilaRestal]

It sounds like the Replace function would do the trick:

Replace(Replace([Stock Number],'[',''),']','')

would strip out the square brackets.

Maybe consider doing that as an update query and getting rid of those square brackets forever

UPDATE [Parts List] SET [Stock Number] = Replace(Replace([Stock Number],'[',''),']','')

 

[AC5FF]

Arrrgghh...

Sounded like a great idea! Just doing an update query to strip out all those brackets.

BUT... Access 2000 - not sure, but I get an error running this query. Still researching this, but I think if I am reading the error messages right - Replace is an undefined function!

Go Figure!

 

[VilaRestal]

Hmmm, it works in 2007. I've not got an install of 2000 to check. I'm surprised Access 2000 doesn't have that function in queries.

You could write your own VBA function to do it:

Public Function Replace2(ByVal strIn As String, ByVal strReplace As String, ByVal strWith As String) As String
    Replace2 = Replace(strIn, strReplace, strWith)
End Function

and then do Replace2(Replace2([Stock Number],'[',''),']','')

That should work.

(Although it does belong on daily-wtf. Replacing one in built function with another like that...)

 

[Galaxiom]

I agree with plog. What you are doing is a clumsy workaround for a structural defect in your data model.

The substitution section of the part number should absolutely, definitely be a separate field. Then the query you are trying to build would become trivial and run a hundred times faster.

BTW If the S was always a part of the substitution field I would not store it but add it in the format of the complete part number for display.