Sorting out duplicates for every date

[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!

 

[louisjohnphillips]

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!

Have you considered one of the following?

SELECT DISTINCT LoadingDate, TruckNumber
from DispatchOrders

or

SELECT LoadingDate, TruckNumber
from DispatchOrders
group by LoadingDate, TruckNumber

 

[Fredrik]

Thanks for helping a "noob" out!

"(e-mail address removed)" skrev:

 

[Fredrik]

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:

 

[louisjohnphillips]

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 -

I'd try:

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