Report based on multiple query's

[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

 

[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
__________________________________________

 

[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

 

[Tom Wickerath]

Glad to read that you got it working. Good luck on your project!


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