query on summation

[alienscript]

Hello Mdb Gurus,

I have a huge database but this is only a sample. Basically, I want to combine the monthly_qty, annual_qty_used and months_of_use for the similar Part-Numbers (ie. with or without the 'W' suffix). Example, part numbers 0010-20524 and 0010-20524W should be treated as alike.

The Table 'wrong_outcome' generated from the saved query would not be able to handle this kind of situation. The correct Table should be exactly like the one I created: 'should-be_outcome' but I have no least idea how to query this. Can someone help me with a query or SQL.

Any help is very much appreciated. Many thanks.

 

[AlanJ10]

This is pretty horrible, but it seems to work. I don't know if there's a better way to so what you're asking (at least not with SQL, it might look a bit tidier in VBA).

SELECT [Table1].[Part_Number], sum([past_record].[200606]) AS Sum200606, sum([past_record].[200607]) AS Sum200607, sum([past_record].[200608]) AS Sum200608, sum([past_record].[200609]) AS Sum200609, sum([past_record].[200610]) AS Sum200610, sum([past_record].[200611]) AS Sum200611, sum([past_record].[200612]) AS Sum200612, sum([past_record].[200701]) AS Sum200701, sum([past_record].[200702]) AS Sum200702, sum([past_record].[200703]) AS Sum200703, sum([past_record].[200704]) AS Sum200704, sum([past_record].[200705]) AS Sum200705, Sum200606+Sum200607+Sum200608+Sum200609+Sum200610+Sum200611+Sum200612+Sum200701+Sum200702+Sum200703+Sum200704+Sum200705 AS annual_qty_usage, iif(nz(Sum200606,0)=0,0,1)+iif(nz(Sum200607,0)=0,0,1)+iif(nz(Sum200608,0)=0,0,1)+iif(nz(Sum200609,0)=0,0,1)+iif(nz(Sum200610,0)=0,0,1)+iif(nz(Sum200611,0)=0,0,1)+iif(nz(Sum200612,0)=0,0,1)+iif(nz(Sum200701,0)=0,0,1)+iif(nz(Sum200702,0)=0,0,1)+iif(nz(Sum200703,0)=0,0,1)+iif(nz(Sum200704,0)=0,0,1)+iif(nz(Sum200705,0)=0,0,1) AS Months_of_Use INTO wrong_outcome
FROM Table1 LEFT JOIN past_record ON [past_record].[Part_Number] Like '*'+[Table1].[Part_Number]+'*'
GROUP BY [table1].[Part_Number]
ORDER BY [Table1].[Part_Number];

This will give a 0, rather than Null for Months_of_use where there aren't any months of use. Let me know if that's a problem.

 

[alienscript]

Hello Alan,

I tried but without luck. It says: MSAccess cant represent joint expression [past_record].[Part_Number] Like'*'+[Table1].[Part_Number]+'*' in design view. When i try to go to datasheet View, it says: Undefined function 'sum' in expression. If the original query worked when you did it, could you send me the .mdb sample? many Thanks Alan

 

[AlanJ10]

I get the same error in Design View, so it would seem that you can only create joins like this in the SQL view.

I also got a syntax error when trying to go into datasheet view, because a few spaces have managed to creep into the SQL if you copy and paste it from my last post.

I've attached your database and replaced the query that was already in it with the working one, so hopefully this will be ok.

I'm a bit concerned by the undefined function error as I can't reproduce that. Out of interest, what version of Access are you using.

 

[alienscript]

It's alright I can only use SQL view. It works fine and now I can use it to my huge database. I am using Access 2003.
Alan, Thanks again.

 

[alienscript]

Hello Alan,

sorry I forgot to tell you that the summation should only work for Part_Number with suffix 'W'. In my 2nd example the Part_Number has suffix 'B' and in deed in my database the Part_Numbers have many other suffixes, eg. R, T, CR, BX, 176500FA, etc.

I tried to amend this line as below but no luck:
FROM Table1 LEFT JOIN past_record ON past_record.Part_Number Like 'W'+Table1.Part_Number+'W'

How should the SQL be modified?

[SQL syntax]
SELECT Table1.Part_Number, sum(past_record.[200606]) AS Sum200606, sum(past_record.[200607]) AS Sum200607, sum(past_record.[200608]) AS Sum200608, sum(past_record.[200609]) AS Sum200609, sum(past_record.[200610]) AS Sum200610, sum(past_record.[200611]) AS Sum200611, sum(past_record.[200612]) AS Sum200612, sum(past_record.[200701]) AS Sum200701, sum(past_record.[200702]) AS Sum200702, sum(past_record.[200703]) AS Sum200703, sum(past_record.[200704]) AS Sum200704, sum(past_record.[200705]) AS Sum200705, Sum200606+Sum200607+Sum200608+Sum200609+Sum200610+Sum200611+Sum200612+Sum200701+Sum200702+Sum200703+Sum200704+Sum200705 AS annual_qty_usage, IIf(nz(Sum200606,0)=0,0,1)+IIf(nz(Sum200607,0)=0,0,1)+IIf(nz(Sum200608,0)=0,0,1)+IIf(nz(Sum200609,0)=0,0,1)+IIf(nz(Sum200610,0)=0,0,1)+IIf(nz(Sum200611,0)=0,0,1)+IIf(nz(Sum200612,0)=0,0,1)+IIf(nz(Sum200701,0)=0,0,1)+IIf(nz(Sum200702,0)=0,0,1)+IIf(nz(Sum200703,0)=0,0,1)+IIf(nz(Sum200704,0)=0,0,1)+IIf(nz(Sum200705,0)=0,0,1) AS Months_of_Use INTO combined_outcome
FROM Table1 LEFT JOIN past_record ON past_record.Part_Number Like '*'+Table1.Part_Number+'*'
GROUP BY table1.Part_Number
ORDER BY Table1.Part_Number;

 

[AlanJ10]

Right, it's getting even messier now.

I'll just attach the database rather than paste the SQL as I think it's getting even bigger. I've changed your query so that it now makes a table identical to the 'Should-be'_outcome table (apart from the field names).

I've changed it so that it now takes the part numbers from the past_record table rather than table1 (so it's a right join rather than a left join) as that appears to be what you want. Any part numbers that end in a 'W' will have the 'W' dropped and so will be grouped together with the part number that doesn't have a 'W'. Any other suffixes will be treated as two different part numbers.

 

[alienscript]

Alan, you're truly wonderful ;-) I cant tell you how much I appreciate it! Thanks a zillion!!

 

[Rich]

Why do you have so many tables and why are you saving calculated values?