Experts needed: Summary challenge

[TinkerMan]

Greetings

(Thanks for an excellent forum, by the way!)

I have a 2,5/3 dimensional problem, which my lack of solving, is taken out on my hair

I'm working with A2k, more specifically a production system. The major thing is the orders. The "special" thing is that the orders are for whole weeks at the time, where the number of ordered items varies for each day.

To make viewing/editing/creation of orders simpler the table ended up looking like this:
OrderIs - Autonumber (primary key)
ItemId, CutomerID - Integer (foreign keys)
Mon, Tue, Wed, Thu, Fri, Sat - Integer (number of items for each day)
StartDate, EndDate - Date (when the order starts and stops)
Order type - String (Let's say types A and B for simplicity)

One order is therefore one row, with one column for each day. There is currently no constraints on how many orders a customer can have for an item (there might be correction orders).

Presenting this for display and editing works well. My problem starts when i want to summarise the information. This is how I want it summarised:
Item...Mon(A)...Mon(B)...Sum Mon...Tue(A)...Tue(B)...Sum Tue...etc
==================================================
i1......50..........10.........60............55.........0..........55.....etc
i2......20..........30.........50............10.........30........40.....etc

Not only do I neeed to summarise the number of items orderd (grouped on item), but I need to seperate out the sums for the two different types. Summarising both type A and B into one sum is simple, but when I started to try and acheive the split, it rather quickly stopped up. The sum for each day can be disregarded, as that can be an expression field directly on the form.

If I introduce the type to be grouped on, it will create the split I need, but it will be on two different rows (one for each type).

Also in many cases there will be not be orders of both types (but there might), so the solution needs to "fill in blanks" (0's) (like in the example for i1 on Tuesday of type B), for types that does not exist.

Bonus question: The query I use needs a date to filter out the orders for the week of the date. In the begining I used now(), but that is not very flexible. Does anybody know how to send a parameter to a query without bothering the user (also that the query needs to be operated from different forms). My solution has been to create a function (parameterised) that generates the sql and then in the OnOpen event set me.RecordSource with the result. I imagine this summarising to get complicated, so I wouldn't mind a more static query, but as I do not know how to send parameters to static queries.....

Please feel free to suggst solutions or changes that get's me closer to a solution. I don't feel overly confident about my datamodel decision of having a number of items for each day, but i did not see another solution.

Thanks guys and gals

 

[TinkerMan]

Thanks for the swift reply Pat

Yes, it sounds better as well. I have done a quick test and yes, using a crosstab will transpose back to what I already had.

Q1: As all orders are of the same type and item, would it be wrong/better/worse to put the Type and ItemID into the order table? As I see it i need to join the two tables when doing most suff and it doesn't seem to matter where they come from (so far...)

Q2: Back to my summarisation problem: I'm not well versed in crosstabs, but I was able to produce one that summarised the order quantity, by using ItemID as the row heading and DayNum as the Column Heading. However, this still leaves out the Type differenciation. I tried to add it as a second row header and i then got one sumarised row for type A and one for B, meaning I'm back to square one. What am I missing here?

 

[TinkerMan]

Excellent Pat

(Swift should be you middle name!)

I tried it and it works!! I get the columns exactly where I want

However, one small thing is still remaining. As there order and ItemDays contains orders for a long time period, the query needs to filter out those orders that are valid for at least the week in question (calculating the whole year all the time would kill performance). So in my test query i have used "where order.start_date < now() AND order.end_date > now()". However, those in charge of the production will need to peak ahead to date not necessarily in this week. That means that the now() must be parameterised to a date coming from the form. Anybody a clue on how to do this? I would like it if it could be taken from the form and not a dialog, as I would like to present a drop down of dates and also the possibility to change the date after they have opened the form....

The solution I had before the outer join, crosstab and union, was a function to generate the sql based on parameters, and in the event onOpen put it as the me.RecordSource. I can't see how all the additional crosstabs and unions can rely on a dynamic on-the-fly query....

Thanks -)