Report based on multiple query's

E

Erwin Bormans

Hi all

If got a table with orders. These orders are linked to a customer. If the
total cost of an order is 0, its a replacement of a previous order that went
wrong. All the orders got a delivery date, but sometimes this date is not
known at the moment the order is entered, it this is the case we fill in
9/09/9999 as date.

Now I want to make a report per customer that gives a vision on the total
amount and price, grouped by delivery date of the normal orders. Than of the
replacements and then of the normal orders on date 9/09/9999 and then the
replacements on date 9/09/9999.

The database excist with 2 tables: Ordergeg1 (Orders) and Klanten_Excel
(Customers)

I've made 4 query's: (Klanten = Customer)
1: Filter all the normal orders (PTotaal <> 0 and delivery date <>9/09/9999)

SELECT Klanten_Excel.KLANTNR, Klanten_Excel.NAAM_1,
Klanten_Excel.BEZ_STRAAT, Klanten_Excel.BEZ_HUISNR, Klanten_Excel.BEZ_PCODE,
Klanten_Excel.BEZ_PLAATS, Klanten_Excel.BEZ_LAND, ORDERGEG1.LEVERDATUM,
Sum(ORDERGEG1.PTOTAAL) AS SomVanPTOTAAL, Sum(ORDERGEG1.TOTVM) AS
SomVanTOTVM, Sum(ORDERGEG1.TOTBEREKVM) AS SomVanTOTBEREKVM,
Sum(ORDERGEG1.KORTING) AS SomVanKORTING, ORDERGEG1.STATUS
FROM Klanten_Excel INNER JOIN ORDERGEG1 ON
Klanten_Excel.KLANTNR=ORDERGEG1.KLANTNR
WHERE (((ORDERGEG1.PTOTAAL)<>0))
GROUP BY Klanten_Excel.KLANTNR, Klanten_Excel.NAAM_1,
Klanten_Excel.BEZ_STRAAT, Klanten_Excel.BEZ_HUISNR, Klanten_Excel.BEZ_PCODE,
Klanten_Excel.BEZ_PLAATS, Klanten_Excel.BEZ_LAND, ORDERGEG1.LEVERDATUM,
ORDERGEG1.STATUS
HAVING (((Klanten_Excel.BEZ_LAND)="BE") AND
((ORDERGEG1.LEVERDATUM)<>#9/9/9999#) AND ((ORDERGEG1.STATUS)=1));

2: Filter all the replacement orders (PTotaal = 0 and delivery date <>
9/09/9999)

SELECT Klanten_Excel.KLANTNR, Klanten_Excel.BEZ_LAND, ORDERGEG1.LEVERDATUM,
Sum(ORDERGEG1.PTOTAAL) AS SomVanPTOTAAL, Sum(ORDERGEG1.TOTVM) AS
SomVanTOTVM, Sum(ORDERGEG1.TOTBEREKVM) AS SomVanTOTBEREKVM,
Sum(ORDERGEG1.KORTING) AS SomVanKORTING, ORDERGEG1.STATUS
FROM Klanten_Excel INNER JOIN ORDERGEG1 ON Klanten_Excel.KLANTNR =
ORDERGEG1.KLANTNR
WHERE (((ORDERGEG1.PTOTAAL)=0))
GROUP BY Klanten_Excel.KLANTNR, Klanten_Excel.BEZ_LAND,
ORDERGEG1.LEVERDATUM, ORDERGEG1.STATUS
HAVING (((Klanten_Excel.BEZ_LAND)="BE") AND
((ORDERGEG1.LEVERDATUM)<>#9/9/9999#) AND ((ORDERGEG1.STATUS)=1));

3: Filter all the normal orders on date 9/09/9999 (PTotaal <> 0 and delivery
date = 9/09/9999)

SELECT Klanten_Excel.KLANTNR, Klanten_Excel.BEZ_LAND, ORDERGEG1.LEVERDATUM,
Sum(ORDERGEG1.PTOTAAL) AS SomVanPTOTAAL, Sum(ORDERGEG1.TOTVM) AS
SomVanTOTVM, Sum(ORDERGEG1.TOTBEREKVM) AS SomVanTOTBEREKVM,
Sum(ORDERGEG1.KORTING) AS SomVanKORTING, ORDERGEG1.STATUS,
ORDERGEG1.ORDERNR, ORDERGEG1.REFERENTIE, ORDERGEG1.ONTVDATUM
FROM Klanten_Excel INNER JOIN ORDERGEG1 ON Klanten_Excel.KLANTNR =
ORDERGEG1.KLANTNR
WHERE (((ORDERGEG1.PTOTAAL)<>0))
GROUP BY Klanten_Excel.KLANTNR, Klanten_Excel.BEZ_LAND,
ORDERGEG1.LEVERDATUM, ORDERGEG1.STATUS, ORDERGEG1.ORDERNR,
ORDERGEG1.REFERENTIE, ORDERGEG1.ONTVDATUM
HAVING (((Klanten_Excel.BEZ_LAND)="BE") AND
((ORDERGEG1.LEVERDATUM)=#9/9/9999#) AND ((ORDERGEG1.STATUS)=1));

4: Filter all replacements on date 9/09/9999 (PTotaal = 0 and delivery date
= 9/09/9999)

SELECT Klanten_Excel.KLANTNR, Klanten_Excel.BEZ_LAND, ORDERGEG1.LEVERDATUM,
Sum(ORDERGEG1.PTOTAAL) AS SomVanPTOTAAL, Sum(ORDERGEG1.TOTVM) AS
SomVanTOTVM, Sum(ORDERGEG1.TOTBEREKVM) AS SomVanTOTBEREKVM,
Sum(ORDERGEG1.KORTING) AS SomVanKORTING, ORDERGEG1.STATUS,
ORDERGEG1.ORDERNR, ORDERGEG1.REFERENTIE, ORDERGEG1.ONTVDATUM
FROM Klanten_Excel INNER JOIN ORDERGEG1 ON Klanten_Excel.KLANTNR =
ORDERGEG1.KLANTNR
WHERE (((ORDERGEG1.PTOTAAL)=0))
GROUP BY Klanten_Excel.KLANTNR, Klanten_Excel.BEZ_LAND,
ORDERGEG1.LEVERDATUM, ORDERGEG1.STATUS, ORDERGEG1.ORDERNR,
ORDERGEG1.REFERENTIE, ORDERGEG1.ONTVDATUM
HAVING (((Klanten_Excel.BEZ_LAND)="BE") AND
((ORDERGEG1.LEVERDATUM)=#9/9/9999#) AND ((ORDERGEG1.STATUS)=1));


Now I want to build a report that gives me all the customers details on top,
than all the normal orders, the replacements, the normal orders on 9/09/9999
and the replacements on 9/09/9999.

Example
__________________________________________________________________________________________
Customer Nr.: 0001
Customer Name: Erwin Bormans
Customer Adres: bla 4, 0000, City

Normal orders:
Delivery date PTotaal TotAmount TotCalculatedAmout TotDiscount
1/12/2007 12,02 15 16,02
0
10/12/2007 327,09 40,23 41
0

Replacements
Delivery date PTotaal TotAmount TotCalculatedAmout TotDiscount
5/12/2007 0 5,2 6
0
10/12/2007 0 4,2 4,5
0

Normal orders on 9/09/9999
OrderNr; Reference. PickupDate Delivery date PTotaal
TotAmount TotCalculatedAmout TotDiscount
1 4X56YH 9/12/2007 9/09/9999 215,56 15,2
16 0
223 FD543Z 10/12/2007 9/09/9999 43,56 44,2
44,5 0

Replacements on 9/09/9999
OrderNr; Reference. PickupDate Delivery date PTotaal
TotAmount TotCalculatedAmout TotDiscount
123 3ED45J8 1/01/2008 9/09/9999 0
2,2 3 0
_____________________________________________________________________________________________
Next customer on next page

Is there a possible way to put 4 querys on 1 report? Maybe work with
subreports in new window (but this button is disabled in Access)?

Any help would be very welcome!!

Thx in advance

Kind regards
Erwin
 
T

Tom Wickerath

Hi Erwin,
Is there a possible way to put 4 querys on 1 report? Maybe work with
subreports in new window (but this button is disabled in Access)?

Yes, you can use a separate subreport, binding each query to a different
report. Then, after you have each report working correctly, add these as
separate sub-reports to your main report. However, you should be able to do
this using grouping and sorting in one report (no subreports required). I am
definately not in favor of the idea of adding bogus data (9/09/9999) when a
delivery date is unknown. Why not just leave the delivery date null?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
E

Erwin Bormans

Hi Tom

Thx for the reply.

Problem is fixed with subreports...

I'm not in favor of the idea either, but the company works likes this for
many years so its out of my hands. Its the intention to change this in the
future :)

Kind regards
Erwin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top