Generating total using a query

[Leao via AccessMonster.com]

Hello all,

Ok, first things first, thanks for your help whoever you are, I have looked
thru this board enough to know that the help given here is first class and
very professional.

That said. I am trying to generate a report that displays a volume and a
couple of dates. I want to use a combo box to choose which Area will be in
the calculation. Each Area is divided into different Sites. I have two tables
with the Area as the Parent joined to the other table with the Sites as the
child using a one-to-many join (one Area to many Sites). I want to total all
the Sites volumes from a field in the Sites table and display one volume for
the entire Area. I get multiple reports now, (the same number Sites within
the Area). I need one report that displays the total volume and also provides
the dates the work started in the Area (based on a field in the Sites table)
and the date work is finished in the Area (also a field in the Sites table).
I know the ending date will need to be based on a statement that is something
like If field is null display nothing else display last date. One other
problem is that some of the Sites are in two different Areas. Say there is a
Site called QQ- 1A thru QQ-4A, well QQ-1A is in area ZZ but the other three
Sites are in Area WW, I just want the volume total for the Sites in each Area.


I thought about using an update query to store the totals in the Area table
in a new field, but after reading thru this board I learned that is not wise,
but to just have the query do all the work each time it is run. So now I am
stuck on which way to turn and hwo best to proceed.

Leao

 

[Michel Walsh]

Hi,


If your data is like:

data ' table name
Site DateStamp Volume ' fields
QQ1A 2005.01.01 16
QQ1A 2005.01.03 8
QQ1B 2005.01.02 1
QQ1C 2005.01.05 2
QQ1D 2005.01.02 4
QQ1D 2005.01.03 5



and if you have a table

Areas ' table name
Area Site ' fields
one QQ1A
two QQ1B
two QQ1C
two QQ1D


then

================
SELECT area, MIN(dateStamp), MAX(dateStamp), SUM(volume)
FROM areas INNER JOIN data
ON areas.site=data.site
GROUP BY area
================

returns

one 2005.01.01 2005.01.03 24
two 2005.01.02 2005.01.05 12


IF you also want the total by site, change your Areas table to:

Areas ' table name
Area Site ' fields
one QQ1A
two QQ1B
two QQ1C
two QQ1D
QQ1A QQ1A
QQ1B QQ1B
QQ1C QQ1C
QQ1D QQ1D


which is, in fact, just a data addition: we include each site as an area.
The query stays the same, listing the sum of the volumes, as well as the
earliest and latest date involved on each "area" (now also including
individual sites as well as aggregation of sites).



Hoping it may help,
Vanderghast, Access MVP

 

[Leao via AccessMonster.com]

Thanks Michel,

That does help.
I do have my Site table
Site Begin Date End Date c_Area Volume
QQ1A 4/20/2006 5/2/2006 ZZ 23
QQ1B 4/2/2006 4/22/2006 ZZ 60
QQ1C 3/20/2006 4/25/2006 WW 12
QQ1D 3/22/2006 5/1/2006 ZZ 90

With my Area table
Area Site
ZZ QQ1A
ZZ QQ1B
ZZ QQ1D
WW QQ1C

I guess I should have posted this first. Will the code still work with my
tables this way, and if so where do I put the code?

Thanks again!

Hi,

If your data is like:

data ' table name
Site DateStamp Volume ' fields
QQ1A 2005.01.01 16
QQ1A 2005.01.03 8
QQ1B 2005.01.02 1
QQ1C 2005.01.05 2
QQ1D 2005.01.02 4
QQ1D 2005.01.03 5

and if you have a table

Areas ' table name
Area Site ' fields
one QQ1A
two QQ1B
two QQ1C
two QQ1D

then

================
SELECT area, MIN(dateStamp), MAX(dateStamp), SUM(volume)
FROM areas INNER JOIN data
ON areas.site=data.site
GROUP BY area
================

returns

one 2005.01.01 2005.01.03 24
two 2005.01.02 2005.01.05 12

IF you also want the total by site, change your Areas table to:

Areas ' table name
Area Site ' fields
one QQ1A
two QQ1B
two QQ1C
two QQ1D
QQ1A QQ1A
QQ1B QQ1B
QQ1C QQ1C
QQ1D QQ1D

which is, in fact, just a data addition: we include each site as an area.
The query stays the same, listing the sum of the volumes, as well as the
earliest and latest date involved on each "area" (now also including
individual sites as well as aggregation of sites).

Hoping it may help,
Vanderghast, Access MVP

Hello all,

[quoted text clipped - 39 lines]

Leao

 

[Michel Walsh]

Hi,


Sure, should work. You place the code in the SQL view of a query. Since you
have all you need in Site table, use the graphical capabilities: make a new
query, bring the table Site, click on the button with the summation symbol
to get a "total" query, drag field c_area in the grid, keep the proposed
GroupBy, do the same with BeginDate and EndDate, but now, use MIN instead of
GroupBy for BeginDate, and MAX, for EndDate. Next, bring Volume, and use
SUM. That is that simple.


Vanderghast, Access MVP

Thanks Michel,

That does help.
I do have my Site table
Site Begin Date End Date c_Area Volume
QQ1A 4/20/2006 5/2/2006 ZZ 23
QQ1B 4/2/2006 4/22/2006 ZZ 60
QQ1C 3/20/2006 4/25/2006 WW 12
QQ1D 3/22/2006 5/1/2006 ZZ 90

With my Area table
Area Site
ZZ QQ1A
ZZ QQ1B
ZZ QQ1D
WW QQ1C

I guess I should have posted this first. Will the code still work with my
tables this way, and if so where do I put the code?

Thanks again!

Hi,

If your data is like:

data ' table name
Site DateStamp Volume ' fields
QQ1A 2005.01.01 16
QQ1A 2005.01.03 8
QQ1B 2005.01.02 1
QQ1C 2005.01.05 2
QQ1D 2005.01.02 4
QQ1D 2005.01.03 5

and if you have a table

Areas ' table name
Area Site ' fields
one QQ1A
two QQ1B
two QQ1C
two QQ1D

then

================
SELECT area, MIN(dateStamp), MAX(dateStamp), SUM(volume)
FROM areas INNER JOIN data
ON areas.site=data.site
GROUP BY area
================

returns

one 2005.01.01 2005.01.03 24
two 2005.01.02 2005.01.05 12

IF you also want the total by site, change your Areas table to:

Areas ' table name
Area Site ' fields
one QQ1A
two QQ1B
two QQ1C
two QQ1D
QQ1A QQ1A
QQ1B QQ1B
QQ1C QQ1C
QQ1D QQ1D

which is, in fact, just a data addition: we include each site as an area.
The query stays the same, listing the sum of the volumes, as well as the
earliest and latest date involved on each "area" (now also including
individual sites as well as aggregation of sites).

Hoping it may help,
Vanderghast, Access MVP

Hello all,

[quoted text clipped - 39 lines]

Leao