Prev: error on new record...
Next: Why couldn't exit the MS ACCESS program
From: The Frog on 8 Feb 2010 07:20
Hi Everyone,

I am trying to run a form of comparative analysis between two sets of
tables to see (quickly) if there is a reason to inspect them more
closely. I am basing this on a query rowcount. In one database the
fields I need to tally up are in the one table making this very easy
with the use of a correlated subquery => each year, each week, number
of rows.

In the other database the data (these are actually the origin of the
data) is spread across several tables. I can acquire the 'raw' data
with SQL no problems, but I am buggered if I can create a query that
returns me the same year / week / number of rows data that I generate
with the above single table scenario.

The SQL for the 'raw' data is as follows:

SELECT CDW.RECHNUNGSPOSITIONEN.JAHR AS yr
, CDW.RECHNUNGSPOSITIONEN.KUNDEN_ID AS customer_id
, CDW.PRODUKTE.EAN_VBE AS ean
, CDW.KALENDER.WOCHE AS week
, Sum(CDW.RECHNUNGSPOSITIONEN.ANZ_VSE) AS qty
FROM CDW.RECHNUNGSPOSITIONEN
JOIN CDW.PRODUKTE ON CDW.PRODUKTE.ID =
CDW.RECHNUNGSPOSITIONEN.PRODUKT_ID
JOIN CDW.KALENDER ON CDW.KALENDER.DATUM =
CDW.RECHNUNGSPOSITIONEN.RECHNUNG_DATUM
WHERE CDW.RECHNUNGSPOSITIONEN.JAHR = <Insert year here>
AND CDW.PRODUKTE.SEGMENT Like 'PET%'
AND CDW.KALENDER.WOCHE = <Insert week of year here>
AND CDW.RECHNUNGSPOSITIONEN.AUFTRAGS_ART_ID NOT IN (39,48)
AND CDW.PRODUKTE.EAN_VBE <> 0
GROUP
BY CDW.RECHNUNGSPOSITIONEN.JAHR
, CDW.RECHNUNGSPOSITIONEN.KUNDEN_ID
, CDW.KALENDER.WOCHE
, CDW.PRODUKTE.EAN_VBE

As you can see from the SQL that this will extract data for one week
in one year (JAHR is german for year, and WOCHE is German for week).
This statement works fine. I just cant seem to 'roll it up'. Please
not that simply swapping the Sum() for Count() wont work because there
are several rows in the original data (ie/ the data this query
summarises) for each ean(ANZ_VSE) at each customer (KUNDEN_ID).

The correlated subquery for the 'copy' table (the one I have locally
and need to check against the source / raw) is as follows:

select shipments.yr, shipments.week,
(select count(*) from shipments as T2
where T2.yr = shipments.yr
and T2.week = shipments.week) as qty
from shipments
group by shipments.yr, shipments.week

Pretty simple eh? I would like your help to generate a matching output
directly from the 'raw' set of tables. Can anyone help me? - I cannot
use a view type approach and then query the view, it must be done
direct as a single statement.

Any help would be greatly appreciated.

Cheers

The Frog
From: DFS on 11 Feb 2010 00:23
The Frog wrote:

> Pretty simple eh? I would like your help to generate a matching output
> directly from the 'raw' set of tables. Can anyone help me? - I cannot
> use a view type approach and then query the view, it must be done
> direct as a single statement.


Since you're asking this in an Access newsgroup:

1) link your tables into MS Access if they're not already
2) save your big SQL as a new query
3) write a query against the new query.


Or maybe:

SELECT CDW.RECHNUNGSPOSITIONEN.JAHR AS yr
, CDW.KALENDER.WOCHE AS week
, Count(CDW.KALENDER.WOCHE) AS qty
FROM CDW.RECHNUNGSPOSITIONEN
JOIN CDW.PRODUKTE ON CDW.PRODUKTE.ID =
CDW.RECHNUNGSPOSITIONEN.PRODUKT_ID
JOIN CDW.KALENDER ON CDW.KALENDER.DATUM =
CDW.RECHNUNGSPOSITIONEN.RECHNUNG_DATUM
WHERE CDW.RECHNUNGSPOSITIONEN.JAHR = <Insert year here>
AND CDW.PRODUKTE.SEGMENT Like 'PET%'
AND CDW.RECHNUNGSPOSITIONEN.AUFTRAGS_ART_ID NOT IN (39,48)
AND CDW.PRODUKTE.EAN_VBE <> 0
GROUP BY
CDW.RECHNUNGSPOSITIONEN.JAHR
, CDW.KALENDER.WOCHE


 | 
Pages: 1
Prev: error on new record...
Next: Why couldn't exit the MS ACCESS program