Sorting out duplicates for every date

  • Thread starter Thread starter Fredrik
  • Start date Start date
F

Fredrik

Hello all,

I'm working on a transport-booking system for our Company.
Previously we only used excel, unaware of the power we already had in our
Computers in form of Access.

To the question:

Here's a the table:
Ordernumber Loadingdate Trucknumber
1 19jan OH45LP
2 19jan OH45LP
3 19jan BGLP34
4 23mar BGLP34

From the query I want this result:
19jan OH45LP
19jan BGLP34
23mar BGLP34

In simple words: I want it to show how many trucks that load every day. One
truck can have many orders on it and theese i want sort out so it only shows
the trucknumber once for every day.

How do I do?
Thanks for your help!
 
Hello all,

I'm working on a transport-booking system for our Company.
Previously we only used excel, unaware of the power we already had in our
Computers in form of Access.

To the question:

Here's a the table:
Ordernumber            Loadingdate            Trucknumber
1                                    19jan                 OH45LP
2                                     19jan                 OH45LP
3                                     19jan                BGLP34
4                                     23mar               BGLP34

From the query I want this result:
19jan                         OH45LP
19jan                          BGLP34
23mar                        BGLP34

In simple words: I want it to show how many trucks that load every day. One
truck can have many orders on it and theese i want sort out so it only shows
the trucknumber once for every day.

How do I do?
Thanks for your help!
Click to expand...


Have you considered one of the following?

SELECT DISTINCT LoadingDate, TruckNumber
from DispatchOrders

or

SELECT LoadingDate, TruckNumber
from DispatchOrders
group by LoadingDate, TruckNumber
 
Now it's getting pretty advanced for me.

If I want to have the amount of pallets with the query how do I solve that?
Then I of course want the total amount of pallets and not only from the
"DISTINCT" records´.
I mean:

Ordernumber Pallets Loadingdate Trucknumber
1 3 19jan OH45LP
2 4 19jan OH45LP
3 5 19jan BGLP34
4 6 23mar BGLP34

Output:
Loadingdate Pall Trucknumber
19jan 7 OH45LP
19jan 5 BGLP34
23mar 6 BGLP34

"(e-mail address removed)" skrev:
 
Now it's getting pretty advanced for me.

If I want to have the amount of pallets with the query how do I solve that?
Then I of course want the total amount of pallets and not only from the
"DISTINCT" records´.
I mean:

Ordernumber   Pallets         Loadingdate            Trucknumber
1                        3            19jan                 OH45LP
2                        4            19jan                 OH45LP
3                        5            19jan                BGLP34
4                        6            23mar               BGLP34

Output:
Loadingdate     Pall        Trucknumber
19jan               7          OH45LP
19jan               5           BGLP34
23mar              6          BGLP34

"(e-mail address removed)" skrev:






- Show quoted text -
Click to expand...

I'd try:

SELECT LoadingDate, TruckNumber, sum( Pallets )
from DispatchOrders
groupy by LoadingDate, TruckNumber
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Select, Copy,Paste and Save data from subform in Access 2007. 0
removing duplicates using a criteria 3
Serial number Query 1
Form-Lookup Values, Yes/No Fields, Etc.. 1
Excel Solved - Using Excel 2007 Large()/Small() function without duplicate values! 1
data is repeating itself 25
Karma for O'Leary 3
revers minus 1

Back
Top