Crosstab Queries and Database Design

[Jim S.]

I'm working in Access 2000 with a table of customer orders that has five
fields: OrderID, CustomerID, Amount, Date, and Recurring (Y/N). All orders
that are recurring will recur on an annual basis for the foreseeable future.

Now I want to eventually create a crosstab query for actual and projected
orders through a given date which would include the CustomerID as the row
heading and the Year(Date) as the column heading.

Example:

Table
OrderID CutomerID Amount Date Recurring
1 A $100 5/2/2005 Y
2 B $500 8/5/2005 N
3 C $200 9/30/2007 N

Crosstab Query
2005 2006 2007 2008
A $100 $100 $100 $100
B $500
C $200

My question is from a design perspective, what is the best way to
handle/"create" the additional amounts that are shown for Company A orders
(i.e. the recurring amounts)? Would it be through a function or query to
create additional records in a temporary table? Any guidance or examples you
can provide are greatly appreciated.

 

[KARL DEWEY]

Use an append query with critera for Recurring of "Y" and for Date of <
DateDiff("y", Date(),-1).
Do not append YourDate to YourDate but add field like X: Date() to append.

You should change the field name to something like OrderDate - Date is a
reserved word in Access.

 

[Jim S.]

Thanks, although with my limited knowledge of append queries I need a little
more help.

How would you add a paramater to this append query to indicate the range of
future dates? To follow on my example, I want to append additional records
for CustomerID A for 2006 to 2008 or some fixed number of years (i.e. 3 years
into the future).

Here's the SQL code that I've been trying to get to work.

INSERT INTO Orders ( CustomerID, Amount, Recurring, FutureDate )
SELECT Orders.CustomerID, Orders.Amount, Orders.Recurring, Orders.OrderDate
FROM Orders
WHERE (((Orders.Recurring)=True) AND
((Orders.OrderDate)=DateDiff("y",[InputDate],-1)));

 

[dbahooker]

all you need to do for recurring orders is basically cartesian these;
and then remove exceptions for example

get a list of weeks; a list of weekly orders; you dont join them
together; you cartesian them.

then if you know that a certain week was cancelled; then you need to
account for that also.. because a recurring order means 'every week
except in december'... right?

 

[Jim S.]

The cartesian query was the way to go. My orders recur on an annual basis
and I created a separate table with values of 1-20 and then based the
cartesian product off of a DateDiff between the orderdate and my input date
(i.e. date through which I wanted to forecast). Thanks for all the help.