newbie question about query with more than 1 table

Hello everyone,

The contents of all example tables in this message are shown by way of
csv-file notation so that the data can easily be imported into a database
for testing.

I have a table (named Charges) in which the following example records can fe
found:


Year,Supplier,InvoiceNumber,ClientRef,ArticleNo,Qt y,TotalCharge

2009,6000022,90789212_212723,31578,110122,4,56.20

2009,6000022,90789605_212723,31829,108268,10,524.3 0

2009,6000022,90789605_212723,31829,108292,1,154.31

2009,6000022,90789605_212723,31829,108972,1,154.31

2009,6000022,90789605_212723,31829,108973,1,154.3

2009,6000022,90789605_212723,31829,108974,1,154.31

2009,6000022,90789605_212723,31829,107715,4,107.00

2009,6000022,90789605_212723,31829,108552,8,204.80

2009,6000022,90793126_212723,61040,107719,3,90.72

2009,6000022,90793126_212723,61040,108275,35,1287. 30

2009,6000022,90793126_212723,61040,108296,10,563.4 0


Some of the articles represented in the records are of a specific type and
the costs of these articles are processed on a different account than the
other articles. (To go short: the first mentioned articles are all plastic
bags and carry code 4 in the Articles table below). This is dealt with using
two tables. These tables have the following fields and (example) records
respectively:


Table: Articles

ArticleNo,Description,Code,Amount,DiscountPerc

110122,Cream_soap,5,12.00,32

108268,Handtowel_1,5,50.00,32

108292,Airfreshener,5,150.00,32

108972,Toilet_paper,5,38.00,32

108973,Wiper,5,20.00,36

108974,Handtowel_2,5,35.00,36

107715,Plastic_bag_1,4,38.50,42

108552,Plastic_bag_2,4,35.00,42

107719,Plastic_bag_3,4,35.00,42

108275,ToiletP_XL,5,30.00,32



Table: Codes

Code,Account,Description

4,414220,Cleaning_products

5,414261,Other_chargeable_products

(codes 1, 2 and 3 are not relevant for this purpose)


By now you propably guessed that I work for a cleaning company, I'm in
finance and I work with some rather obsolete software products, the
limitations of which I'm trying to minimize using Access. All true.
Unfortunately no prizes to give away for the right guesses. I hope you didn't
get too discouraged.


I need to assemble the following information.

For each invoice the sum of the charges based on the account attached to the
article.

For checking purposes: Looking at invoicenumber 90789605_212723 you would
end up with an amount of 1141.53 for articles processed on account 414261
and for the articles processed on account 414220 the result would turn out
to be 311.80


I know I should take a course on Access because when more than one table is
involved in a query I can try all I want but I get lost anyway.


I appreciate any answers.

Thanks in advance.

Peter

Peter -

Try this (untested):

SELECT InvoiceNumber, Code.Account, Sum(TotalCharge)
FROM (Charges INNER JOIN Articles on Charges.ArticleNo = Articles.ArticleNo)
INNER JOIN Code ON Articles.Code = Code.Code
Group By InvoiceNumber, Account

And yes, by all means take a class, take the on-line tutorials, and/or read
some books!

--
Daryl S


"Peter" wrote:

Hello everyone,

The contents of all example tables in this message are shown by way of
csv-file notation so that the data can easily be imported into a database
for testing.

I have a table (named Charges) in which the following example records can fe
found:


Year,Supplier,InvoiceNumber,ClientRef,ArticleNo,Qt y,TotalCharge

2009,6000022,90789212_212723,31578,110122,4,56.20

2009,6000022,90789605_212723,31829,108268,10,524.3 0

2009,6000022,90789605_212723,31829,108292,1,154.31

2009,6000022,90789605_212723,31829,108972,1,154.31

2009,6000022,90789605_212723,31829,108973,1,154.3

2009,6000022,90789605_212723,31829,108974,1,154.31

2009,6000022,90789605_212723,31829,107715,4,107.00

2009,6000022,90789605_212723,31829,108552,8,204.80

2009,6000022,90793126_212723,61040,107719,3,90.72

2009,6000022,90793126_212723,61040,108275,35,1287. 30

2009,6000022,90793126_212723,61040,108296,10,563.4 0


Some of the articles represented in the records are of a specific type and
the costs of these articles are processed on a different account than the
other articles. (To go short: the first mentioned articles are all plastic
bags and carry code 4 in the Articles table below). This is dealt with using
two tables. These tables have the following fields and (example) records
respectively:


Table: Articles

ArticleNo,Description,Code,Amount,DiscountPerc

110122,Cream_soap,5,12.00,32

108268,Handtowel_1,5,50.00,32

108292,Airfreshener,5,150.00,32

108972,Toilet_paper,5,38.00,32

108973,Wiper,5,20.00,36

108974,Handtowel_2,5,35.00,36

107715,Plastic_bag_1,4,38.50,42

108552,Plastic_bag_2,4,35.00,42

107719,Plastic_bag_3,4,35.00,42

108275,ToiletP_XL,5,30.00,32



Table: Codes

Code,Account,Description

4,414220,Cleaning_products

5,414261,Other_chargeable_products

(codes 1, 2 and 3 are not relevant for this purpose)


By now you propably guessed that I work for a cleaning company, I'm in
finance and I work with some rather obsolete software products, the
limitations of which I'm trying to minimize using Access. All true.
Unfortunately no prizes to give away for the right guesses. I hope you didn't
get too discouraged.


I need to assemble the following information.

For each invoice the sum of the charges based on the account attached to the
article.

For checking purposes: Looking at invoicenumber 90789605_212723 you would
end up with an amount of 1141.53 for articles processed on account 414261
and for the articles processed on account 414220 the result would turn out
to be 311.80


I know I should take a course on Access because when more than one table is
involved in a query I can try all I want but I get lost anyway.


I appreciate any answers.

Thanks in advance.

Peter

.

Thanks Daryl. This gives me the result that I can use to start building a
more complex query with.



"Daryl S" schreef in bericht
...
Peter -

Try this (untested):

SELECT InvoiceNumber, Code.Account, Sum(TotalCharge)
FROM (Charges INNER JOIN Articles on Charges.ArticleNo =
Articles.ArticleNo)
INNER JOIN Code ON Articles.Code = Code.Code
Group By InvoiceNumber, Account

And yes, by all means take a class, take the on-line tutorials, and/or
read
some books!

--
Daryl S


"Peter" wrote:

Hello everyone,

The contents of all example tables in this message are shown by way of
csv-file notation so that the data can easily be imported into a database
for testing.

I have a table (named Charges) in which the following example records can
fe
found:


Year,Supplier,InvoiceNumber,ClientRef,ArticleNo,Qt y,TotalCharge

2009,6000022,90789212_212723,31578,110122,4,56.20

2009,6000022,90789605_212723,31829,108268,10,524.3 0

2009,6000022,90789605_212723,31829,108292,1,154.31

2009,6000022,90789605_212723,31829,108972,1,154.31

2009,6000022,90789605_212723,31829,108973,1,154.3

2009,6000022,90789605_212723,31829,108974,1,154.31

2009,6000022,90789605_212723,31829,107715,4,107.00

2009,6000022,90789605_212723,31829,108552,8,204.80

2009,6000022,90793126_212723,61040,107719,3,90.72

2009,6000022,90793126_212723,61040,108275,35,1287. 30

2009,6000022,90793126_212723,61040,108296,10,563.4 0


Some of the articles represented in the records are of a specific type
and
the costs of these articles are processed on a different account than the
other articles. (To go short: the first mentioned articles are all
plastic
bags and carry code 4 in the Articles table below). This is dealt with
using
two tables. These tables have the following fields and (example) records
respectively:


Table: Articles

ArticleNo,Description,Code,Amount,DiscountPerc

110122,Cream_soap,5,12.00,32

108268,Handtowel_1,5,50.00,32

108292,Airfreshener,5,150.00,32

108972,Toilet_paper,5,38.00,32

108973,Wiper,5,20.00,36

108974,Handtowel_2,5,35.00,36

107715,Plastic_bag_1,4,38.50,42

108552,Plastic_bag_2,4,35.00,42

107719,Plastic_bag_3,4,35.00,42

108275,ToiletP_XL,5,30.00,32



Table: Codes

Code,Account,Description

4,414220,Cleaning_products

5,414261,Other_chargeable_products

(codes 1, 2 and 3 are not relevant for this purpose)


By now you propably guessed that I work for a cleaning company, I'm in
finance and I work with some rather obsolete software products, the
limitations of which I'm trying to minimize using Access. All true.
Unfortunately no prizes to give away for the right guesses. I hope you
didn't
get too discouraged.


I need to assemble the following information.

For each invoice the sum of the charges based on the account attached to
the
article.

For checking purposes: Looking at invoicenumber 90789605_212723 you would
end up with an amount of 1141.53 for articles processed on account 414261
and for the articles processed on account 414220 the result would turn
out
to be 311.80


I know I should take a course on Access because when more than one table
is
involved in a query I can try all I want but I get lost anyway.


I appreciate any answers.

Thanks in advance.

Peter

.