Oh my goodness, you went way over my head! I have to have a query cuz I
have to export the data to Excel or Quattro Pro. A Report would only print
or export to a text file. I have reports now anyway, I just want to be able
to export from Access and then import in something else. Haven't heard of
the Union query mentioned above your response. I might try that.
Thanks,
Bob
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
If you want to create a report you can use one query and create a
calculated TextBox on the report that calc's the fawn:doe ratio. Just
use your formula in the TextBox's ControlSource property. E.g.:
Does Fawns Bucks Total Fawn
oe
---- ----- ----- ----- --------
ControlSource D F B D+F+B F/IIf(D=0,null,D)
Where: D=the Does column value
F=the Fawns column value
B=the Bucks column value
The TextBox Fawn
ow ControlSource contains the ratio formula. The
IIf() tests for zero. If D=0 a NULL is used in the calculation, which
will produce a NULL result. If you want zero to appear as the result of
a NULL Doe, use the formula: Nz( F/IIf(D=0,NULL,D) , 0 ).
Anywho... Your data is not normalized. Your table should be designed
like this:
RecordNo -- not necessary, but included for form's sake.
DeerType -- a code number referring to a code in a "look-up table."
DeerCount
CountDate -- this is so you can have more than one count per deer type
per date.
Here is the DeerType "look-up table" (DDL):
CREATE TABLE DeerTypes (
TypeID COUNTER , -- Access AutoNumber
DeerType VARCHAR(20) PRIMARY KEY
)
This is so you can store the Deer ID in the DeerTallys table instead of
the DeerType name. Better storage utilization (numeric vs. string).
The DeerTypes table needs a unique index so the following table can use
the TypeID as a foreign key.
CREATE UNIQUE INDEX idxTypeID ON DeerTypes (TypeID)
CREATE TABLE DeerTallys (
RecordNo COUNTER UNIQUE , -- Access AutoNumber
DeerType INTEGER NOT NULL REFERENCES DeerTypes (TypeID) ,
DeerCount INTEGER NOT NULL ,
CountDate DATE ,
CONSTRAINT PK_DeerTallys PRIMARY KEY (DeerType, CountDate)
)
The Primary Key identifies the count per deer type per day.
Having correctly set up your data you can use a cross-tab (aka Pivot
Table) query to get the data in the format you want. The solution to
your question really requires 2 queries: (1) get the summed counts;
(2) get the ratio of fawns to does. It needs 2 qrys because a summed
value is not the same as a ratio value.
Untested queries:
(1) summed counts - this can be used as the report's RecordSource
TRANSFORM SUM(T.DeerCount) AS daValue
SELECT T.RecordNo, SUM(T.DeerCount) As Total,
FROM DeerTallys AS T INNER JOIN DeerTypes AS TY
ON T.DeerType = TY.TypeID
GROUP BY T.RecordNo
PIVOT TY.DeerType
(2) fawn:doe ratio per day - use this if you don't want to use the
report example
SELECT T.CountDate,
SUM(IIf(TY.TypeID=1, T.DeerCount)) As FawnTotal,
SUM(IIf(TY.TypeID=2, T.DeerCount)) As DoeTotal,
SUM(IIf(TY.TypeID=1, T.DeerCount)) /
SUM(IIf(TY.TypeID=2, T.DeerCount)) As Fawn_Doe_Ratio
FROM DeerTallys AS T INNER JOIN DeerTypes AS TY
ON T.DeerType = TY.TypeID
WHERE TY.TypeID In (1, 2)
GROUP BY T.CountDate
HAVING SUM(IIf(TY.TypeID=2, T.DeerCount)) > 0
TypeID -- 1 = fawns; 2 = does
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQIg28IechKqOuFEgEQJNgACgiMvZAU1ExU6t3D6Wj1C4h1FH7PoAnRcm
1rOWY+fNaZqV6O77hYbcjek8
=wyCG
-----END PGP SIGNATURE-----
