Complex Combination - Is this possible?

[CJ]

Hi Groupies

I thought I had solved my problem but it seems not to be the case.

My 2003 database keeps track of who has planted flowers, where and how much
they were paid.
Staff can also make extra money by doing extra jobs such as kitchen duty,
quality check etc.
If somebody is hurt or not feeling well, they might JUST do the extra
duties.

So, I need to combine the planting data from tblDailyBoxes with the extra
income from
tblMiscEarnings and not miss anybody that ONLY has income for a day in
tblMiscEarnings.

For Example

Current Data

tblDaily Boxes
lngEmpID dtmDate sngBoxes strSpecies
12 2/1/10 15 AW
18 2/1/10 12 AW
12 2/2/10 20 LW

tblMiscEarnings
lngEmpID dtmDate lngDuty curPay
12 2/1/10 3 15
18 2/2/10 5 25
26 2/2/10 10 50

Results

lngEmpID dtmDate sngBoxes strSpecies lngDuty curPay
12 2/1/10 15 AW 3
15
18 2/1/10 12 AW
12 2/2/10 20 LW
18 2/2/10 5
25
26 2/2/10 10
50

Of course, I can create this using a report with a subreport but
the users would like to be able to export it out to Excel so that they
can manipulate the data in other ways.

Is this possible at all without being displayed as reports or forms?

 

[CJ]

Wow, thanks Ken! I was expecting a big Tough Luck!!

I had to make a minor correction to your SQL as you were missing
the sngBoxes field from the second SELECT statement.

I knew what was needed but I couldn't figure out how to do
a Left AND a Right join in a Union Query. Thanks for
showing US ALL how to do that. Very cool.

The completed, corrected SQL is below:

SELECT tblDailyBoxes.lngEmpID, tblDailyBoxes.dtmDate,
sngBoxes , strSpecies, lngDuty, curPay
FROM tblDailyBoxes LEFT JOIN tblMiscEarnings
ON tblDailyBoxes.lngEmpID = tblMiscEarnings.lngEmpID
AND tblDailyBoxes.dtmDate = tblMiscEarnings.dtmDate
UNION
SELECT tblMiscEarnings.lngEmpID, tblMiscEarnings.dtmDate, sngBoxes
strSpecies, lngDuty, curPay
FROM tblDailyBoxes RIGHT JOIN tblMiscEarnings
ON tblDailyBoxes.lngEmpID = tblMiscEarnings.lngEmpID
AND tblDailyBoxes.dtmDate = tblMiscEarnings.dtmDate;

 

[KARL DEWEY]

You really should combine your data into a single table like this --
lngEmpID dtmDate TypeWork Quanity strSpecies curPay
12 2/1/10 sngBoxes 15 AW
18 2/1/10 sngBoxes 12 AW
12 2/2/10 sngBoxes 20 LW
12 2/1/10 ngDuty 3 15
18 2/2/10 ngDuty 5 25
26 2/2/10 ngDuty 10 50

Use the union query to populate the table --
SELECT tblDailyBoxes.lngEmpID, tblDailyBoxes.dtmDate,
"sngBoxes" AS TypeWork, sngBoxes AS Quanity, strSpecies, curPay
FROM tblDailyBoxes
UNION
SELECT tblMiscEarnings.lngEmpID, tblMiscEarnings.dtmDate, "lngDuty" AS
TypeWork, lngDuty AS Quanity, strSpecies, curPay
FROM tblDailyBoxes;

 

[CJ]

Hi Karl

Normalization is the primary reason for why I did not combine the data into
one table.

My example is very simplistic of what is actually going on in this database
and
other factors influenced my decision. It's only this one request that has
caused
me any grief, so I think my choice was the correct one.

However, based on future requests, I might reconsider.

 

[CJ]

Thank you for justifying my argument so eloquently Ken.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

I think you've misunderstood what's being modelled here, Karl.

1. sngBoxes is an attribute type, not an attribute value. Its values are
the number of boxes.

2. lngDuty is also an attribute type, not value, and I'd guess it to be a
foreign key referencing the key of a Duties table. It does not seem to
represent quantitative values as it can refer to things like 'kitchen
duty'
which are not easily quantifiable. I guess it could represent a time
duration, but it’s a long integer data type, which doesn't look right for
this.

Each table as it stands correctly models a sub-type of a type WorkLog say,
so
there is a case to be made for a table to model this (super) type. This
would have columns lngEmpID and dtmDate and would be all key, the
two-column
key being referenced by the two-column keys of the tblDaily Boxes and
tblMiscEarnings in one-to-one relationships. By virtue of the combination
of
it being all key and being referenced in one-to-one relationships,
however,
its contribution to the model is limited, and its absence is not a
significant flaw in the model, which will function adequately with the
two
tables as they stand.

To combine both into a single table would require a different structure to
that which you proposed, but the real point is that the table would be
modelling two separate entity types, which is of course only legitimate
where
all columns represent attributes common to both entity types, i.e. it’s a
(super) type of multiple sub-types. The practical implication of a single
table which models two or more entity types and has columns representing
attributes not common to all of them is that it is wide open to bad data.

Ken Sheridan
Stafford, England

You really should combine your data into a single table like this --
lngEmpID dtmDate TypeWork Quanity strSpecies curPay
12 2/1/10 sngBoxes 15 AW
18 2/1/10 sngBoxes 12 AW
12 2/2/10 sngBoxes 20 LW
12 2/1/10 ngDuty 3
15
18 2/2/10 ngDuty 5
25
26 2/2/10 ngDuty 10 50

Use the union query to populate the table --
SELECT tblDailyBoxes.lngEmpID, tblDailyBoxes.dtmDate,
"sngBoxes" AS TypeWork, sngBoxes AS Quanity, strSpecies, curPay
FROM tblDailyBoxes
UNION
SELECT tblMiscEarnings.lngEmpID, tblMiscEarnings.dtmDate, "lngDuty" AS
TypeWork, lngDuty AS Quanity, strSpecies, curPay
FROM tblDailyBoxes;

Wow, thanks Ken! I was expecting a big Tough Luck!!

[quoted text clipped - 88 lines]

.