details of my problem

  • Thread starter Thread starter zaskodnik
  • Start date Start date
Z

zaskodnik

Here are some details:
My query includes all necessary columns:
Customer Name, Territory, Sales Rep, Product ordered, quantity, uni
price, ext. price, shipped on date, required by date, forecast (this i
check box), shipped (checkbox)

The query includes 3 types of data:
1. orders in backlog (when shipped checkbox is not marked and shippe
on date is empty)
2. shipped orders (when shipped checkbox is marked and shipdate i
filled in)
3. forecast (when forecast checkbox is checked)

I need to summarize tha data in one report like this:
_______Backlog_____Shipped_____Forecasted___Total
Korea___2__________4___________5____________11
Europe__1__________7___________7____________15
Japan___6__________7___________6____________19
Total___9__________18__________18___________45

Right now I can do separate 3 small reports where I use 3 queries tha
give me only backlog, or only shipments, or forecasts.... so I can mak
only 1 column of the needed report.

Any suggestions?

Thank you
 
Hi.
This is a pefect candidate for a crosstab query.
Territory for row
Your three outlined conditions as column
and the count of orders as value.
Now for the three outlined conditions I woul probably use
an IIF statement sun as:
IIF(ShipCheck is null and ShipDate is null,"Backlog",IIF
(ShipCheck is not null and ShipDate is not
null,"Shipped",IIF(Forecast is not
null,"ForeCast","Other")))
All of the above on one line. Note, I also included an
other just to catch any items where the listed conditions
may not be met. This would catch any errors if your data
entry is not validated very tight.
Hope this helps.
Fons
 
Hi Fons!

Thank you for your help.
I am not sure if I did crosstab query, but I fixed it!
I made new query where I connected everything with territories. (wit
right joins). Is this a crosstab query?
Now I have problems with using this new query in report. But I alread
described my problem in another thread.

Thank you Fons for your help.

Petr
 
Back
Top