×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Make Tabkle query only want to display Distinct row

Make Tabkle query only want to display Distinct row

Make Tabkle query only want to display Distinct row

(OP)
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.

RE: Make Tabkle query only want to display Distinct row

Hi,

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Make Tabkle query only want to display Distinct row

(OP)
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

RE: Make Tabkle query only want to display Distinct row

Just add a Sum() for TP.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Make Tabkle query only want to display Distinct row

(OP)
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.

CODE --> sql

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; 

RE: Make Tabkle query only want to display Distinct row

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

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

RE: Make Tabkle query only want to display Distinct row

(OP)
Hi

Sorry here is the sql with the SUM in them.

CODE --> sql

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]); 

RE: Make Tabkle query only want to display Distinct row

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:

CODE --> SQL

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

RE: Make Tabkle query only want to display Distinct row

(OP)
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


RE: Make Tabkle query only want to display Distinct row

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

RE: Make Tabkle query only want to display Distinct row

(OP)
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

RE: Make Tabkle query only want to display Distinct row

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close