pulling values from a table for the following month

[Guest]

I have a table that has the following fields:
Customer's account number, date (alway shown as month,day 1, year), Sales
amount and inventory value.

Example:

Customer date Sales inventory
123 1/1/2007 $10,000 $100
123 2/1/2007 $20,000 $200
123 3/1/2007 $30,000 $300

I would like to creat a query that give me the customer account, the date,
its associated sales for that month for that acccount, BUT the inventory
value for the FOLLOWING Month.

How do I do that?

thanks

 

[Bob Quintal]

I have a table that has the following fields:
Customer's account number, date (alway shown as month,day 1,
year), Sales amount and inventory value.

Example:

Customer date Sales inventory
123 1/1/2007 $10,000 $100
123 2/1/2007 $20,000 $200
123 3/1/2007 $30,000 $300

I would like to creat a query that give me the customer account,
the date, its associated sales for that month for that acccount,
BUT the inventory value for the FOLLOWING Month.

How do I do that?

thanks

create a query that left joins the table on itself via customer.
alias one as sales and the other as inventory, and bring down the
sales.sales from one instance of the table and inventory.inventory
from the other.
Set the criteria for the inventory.date to dateadd("m",+
1,sales.date)

Bring down any other fields yoiu need, save and run.

And beware of a field called date, since date is a function in
Access, you can get really messed up if Access uses the function
instead of the field. And Access seems to default to the function.

 

[=?iso-8859-1?B?VmFsZW507W4gUGxheeE=?=]

Lumos,

i'd create two queries one returning customer and sales of the
selected month, the other query returning the customer and the
inventory of the next month. After that a third query will use those
two queries related by customer to get the customer, sales and
inventory you want.
This solution will work if you have the month from a control of a
form.

Regards,

Valentín Playá
Sonotronic S.A.
**************************************************