need help with forms and mulitple date query

[neov]

Hey guys....my first foray into the Access section!


I have a table with shipment information - customer numbers, dates, items shipped, quantities, etc, etc...

I have a list of customers, not currenty in a table, and for each customer, I need two things - the sales for each calendar year within their contract dates, and the sales for each 12 month period of their contract dates..

An example would be Customer ABC, contract start date = 2/28/2001, end date = 2/28/2006.

I would need to see the calendar year sales in 2001 (after 2/28), 2002, 2003, and 2004 - up to the current date....

I would also need to see the sales for the 12 month period starting 2/28/01, and each subequent 12 month period.

I was hoping to use a form where the user could input the customer number, the start and end dates of the contract, and perhaps a "data through" field,...but I can't get it to work at all...

Any ideas on how this could be done? I kind of have it working with seperate queries, but i was hoping to do this all at once for each customer.

Thanks!

 

[bat17]

To do calender years is fairly straight forward, you can add a column to a query using the datePart funtion to pick out the year and group on that.
DatePart("yyyy",[SalesDate])

To get the second data you will need another grouped query.
to get the year part you will need to create a function something like

TradingYear: IIf(DateDiff("d",DateSerial(DatePart("yyyy",[SalesDate]),1,1),[SalesDate])>DateDiff("d",DateSerial(DatePart("yyyy",[Form]),1,1),[Form]),DatePart("yyyy",[SalesDate]),DatePart("yyyy",[SalesDate])-1)

Where SalesDate is the field you record the date of sales against and form is the form control with the start date for the contract

I have not been able to test this fuction so it may want some playing with

HTH

Peter

 

[neov]

one of the columns in the table is year, another is month, so that part isn't difficult.....can you "use layman's terms" on that second part? Thanks for the help.

 

[bat17]

you will need to create a query with your sales data table in it.
add the formulation I gave you substituting the field name from the sales table that has your sales date in it.
You can then turn the query into a "Grouped Query".

You will want to group on customer and the new column and to sum the sales column.

You need to create the form to hold your contract dates that you spoke of and the them Formin the function needs to repaced with te name of the "from" control, something like
Forms![SalesChecker]![StartDate]


HTH

 

[neov]

ok, I've scrapped the form idea....

I have the first part working perfectly...upon running the query, it prompts me for a customer number, a start date, and an end date...the query is grouped by year, and gives me appropriate totals, etc, etc..no problems...

The 2nd part is still eluding me...using the same input data, how do I isolate rolling 12 month periods instead of calendar years?

For example, if the contract start/end dates are Feb 10, 2001, and Feb 10, 2006, respectively, then how do I get the query to tell me the sales for each 12 month period starting at Feb 10?

thanks!

 

[bat17]

By using the formula
TradingYear: IIf(DateDiff("d",DateSerial(DatePart("yyyy",[SalesDate]),1,1),[SalesDate])>DateDiff("d",DateSerial(DatePart("yyyy",[ StartDate ]),1,1),[ StartDate ]),DatePart("yyyy",[SalesDate]),DatePart("yyyy",[SalesDate])-1)

Where [SalesDate] is the date from your table. When run this will prompt you for a start date. It will then give a year that the date belongs to. So if you put in a start date of 1 July 2003 then a sale made on the 8 July 2003 will give year 2003 but a sale on 8 June 2003 will give year 2002.

This will allow you to group by “TradingYear”. You will need to add criteria to the Sales Date field as well to limit the number of records returned.

Hope this is clearer

Peter

 

[neov]

works perfect, thank you!