Make Tabkle query only want to display Distinct row

[Cpreston]

Hi

I have a problem which I cannot seem to work out a workaround. In the image below is the query result. However, I only want one row showing with a sum of the PI field. In this instance there are only 2 rows, but in some cases there may be more. The unique field is always the FP field. ANy idease please anyone. Thanks in advance.

 

[SkipVought]

Hi,

Select [Pack Number], Date, WONO, FP, W, T, L, PPP, R, Op, Ex, TM, Sum(Pi)
From [YourTable]
Group By [Pack Number], Date, WONO, FP, W, T, L, PPP, R, Op, Ex, TM

What do you want to do with TP & Tickets?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cpreston]

Hi

Sorry I made a mistake I need PI and TP as a sum.

The problem is due to code changes it is now bringing in 2 rows. When we go to print instead of printing 30 tickets it is doubling and wanting to print 60 tickets. I will try your SQL and see the result.

Thanks

 

[SkipVought]

Just add a Sum() for TP.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Cpreston]

Hi

The query is using 4 other queries that populate the make table query If I add a sum into the query , I get a lot of prompts that even if I leave blank come up with 2 lines and no sum. Here is the SQL for the query I am trying to amend.

SELECT 1 AS [Pack Number], Date() AS [Date], [MP FP].WONO, 
[MP FP].FP, [MP FP].Width AS W, [MP FP].Thickness AS T, [MP FP].Length 
AS L, [MP FP].PPP, [MP R].Rips AS R, [MP P].Operation AS Op, [MP S].
[Packs In] AS PI, [MP S].Sheets AS S, [MP S].Ex, [MP S].[Timber Mark] 
AS TM, [S]*[PI]*[R] AS TP, -Int(-[TP]/[ppp]) AS Tickets INTO Pack

FROM [MP S] RIGHT JOIN ([MP R] RIGHT JOIN ([MP FP] LEFT JOIN [MP P] ON 
[MP FP].WONO = [MP P].WONO) ON [MP R].WONO = [MP FP].WONO) ON [MP 
S].WONO = [MP FP].WONO;

 

[dhookom]

There are no SUM()s in the SQL you provided.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Cpreston]

Hi

Sorry here is the sql with the SUM in them.

SELECT 1 AS [Pack Number], Date() AS [Date], [MP FP].WONO, 
[MP FP].FP, [MP FP].Width AS W, [MP FP].Thickness AS T, [MP FP].Length 
AS L, [MP FP].PPP, [MP R].Rips AS R, [MP P].Operation AS Op, Sum([MP 
S].[Packs In]) AS PI, [MP S].Sheets AS S, [MP S].Ex, [MP S].[Timber 
Mark] AS TM, Sum([S]*[PI]*[R]) AS TP, -Int(-[TP]/[ppp]) AS Tickets INTO 
Pack

FROM [MP S] RIGHT JOIN ([MP R] RIGHT JOIN ([MP FP] LEFT JOIN [MP P] ON 
[MP FP].WONO = [MP P].WONO) ON [MP R].WONO = [MP FP].WONO) ON [MP 
S].WONO = [MP FP].WONO

GROUP BY 1, Date(), [MP FP].WONO, [MP FP].FP, [MP FP].Width, [MP 
FP].Thickness, [MP FP].Length, [MP FP].PPP, [MP R].Rips, [MP 
P].Operation, [MP S].Sheets, [MP S].Ex, [MP S].[Timber Mark], -Int(-
[TP]/[ppp]);

 

[dhookom]

You have expressions that use aliases which is not a good practice and is probably causing "a lot of prompts".

You have sums of sums which I'm not sure how they will resolve. Minimally you need to replace the aliases with the expressions. Something like this might work:

SELECT 1 AS [Pack Number], Date() AS [Date], [MP FP].WONO, [MP FP].FP, 
[MP FP].Width AS W, [MP FP].Thickness AS T, [MP FP].Length AS L, 

[MP FP].PPP, [MP R].Rips AS R, [MP P].Operation AS Op, Sum([MP S].
[Packs In]) AS PI, [MP S].Sheets AS S, [MP S].Ex, [MP S].[Timber Mark] 
AS TM, 

Sum([MP S].Sheets * Sum([MP S].[Packs In]) * [MP R].Rips) AS TP, -Int(-
(Sum([MP S].Sheets * Sum([MP S].[Packs In]) * [MP R].Rips))/[ppp]) AS 
Tickets 

INTO Pack

FROM [MP S] RIGHT JOIN ([MP R] RIGHT JOIN ([MP FP] LEFT JOIN [MP P] ON 
[MP FP].WONO = [MP P].WONO) ON [MP R].WONO = [MP FP].WONO) ON [MP 
S].WONO = [MP FP].WONO

GROUP BY 1, Date(), [MP FP].WONO, [MP FP].FP, [MP FP].Width, [MP 
FP].Thickness, [MP FP].Length, [MP FP].PPP, [MP R].Rips, [MP 
P].Operation, 

[MP S].Sheets, [MP S].Ex, [MP S].[Timber Mark], -Int(-(Sum([MP 
S].Sheets * Sum([MP S].[Packs In]) * [MP R].Rips))/[ppp]);

Also, please add some carriage returns in your SQL so the lines aren't so long.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Cpreston]

Hi

Thanks for the reply's. Unfortunately dhookom the code as not worked. It does not like SUM in the group by, I think. I tried changing the code to take the sum out of the group by but still got the same message. I appreciated the code is not the best, but it is an old system made by an ex colleague.
Any further ideas would be appreciated, I will continue to try and adjust the code. Thanks

 

[dhookom]

You can probably resolve this by using a couple queries. Do the first level of aggregation and aliasing. Then build another query off the first.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Cpreston]

Hi

Thanks for all the reply's much appreciated. I managed in the end to add some SUM fields in the fields used in the form the user use for this.

Thanks

 

[Cpreston]

Hi

Sorry about this but the problem unfortunately was not solved. The issue originally was I need to sum things so it gave me one line of totals so the print out of labels gave me the correct amount.
I could not sum due to the TM codes being different. Because on the label it does not matter what it shows for the TM I did a Not Like statement (Not Like MDF/CW) to take one of them out and then did the sum on the form used. This worked, however, we now get one line entry's that have the MDF/CW code in just one lined. So these throw out an error at the label print because of course the Not Like statement takes it out. I am struggling with how to get round this.

If the Pack table as more than one row and as both in, then I need it to ignore one of the codes and sum. If it as just one row then it must allow the codes. Any ideas please.

I have tried to do a separate query but again due to the possibility of 2 different codes then it adds 2 lines in so cannot sum properly. Any ideas please Thanks