Make-Table Problem

  • Thread starter Thread starter Tim C via AccessMonster.com
  • Start date Start date
T

Tim C via AccessMonster.com

hello:

Is it possible to have a make-table query create multiple records from one
record if a certain condition is met?

I have a table with towns and % of trash hauled. My report works fine if
there was only one town with 100% of the load. However, if the load is split
60% from town A, 40% from town B I can't correctly attribute the 2nd town and
their % on the report.

1st TOWN
Org_Code1 =Freeport
OrgPercent1 = 60%

2nd TOWN
Org_Code2 = Portland
OrgPercent2 = 40%

I suppose I could make a new table from the query and use VBA to split the
single record into 2 records where Org_code1 and Orgpercent1 contained the
datareference above but, I would think there should be an easier way using
only a query.

Any Suggestions?

Thanks
TMC
orgpercent1 contained the split data
 
Dear Tim:

Please hear a bit of analysis first.

1. A make-table will make just what it sees in your SELECT statement. So
what you need is a SELECT query that does what you want. I will proceed
from that point.

2. You must, therefore, get two rows in the query for the one row of data.
This is possible!

I cannot see everyting you have, but here it is in principle:

SELECT Org_Code1, OrgPercent1
FROM YourTable
WHERE Org_Code1 IS NOT NULL
AND OrgPercent1 IS NOT NULL

This should produce some of the rows you need.

SELECT Org_Code2, OrgPercent2
FROM YourTable
WHERE Org_Code2 IS NOT NULL
AND OrgPercent2 IS NOT NULL

This should produce the rest of the rows you need.

Now the IS NOT NULL omits those rows where there are not both entries made.

So, put this all together:

SELECT Org_Code1, OrgPercent1
FROM YourTable
WHERE Org_Code1 IS NOT NULL
AND OrgPercent1 IS NOT NULL
UNION ALL
SELECT Org_Code2, OrgPercent2
FROM YourTable
WHERE Org_Code2 IS NOT NULL
AND OrgPercent2 IS NOT NULL

You may add other column to the two queries that make up the union, but add
the same ones to both, and in the same order.

Does this work for you?

Now, the problem is your database design. There needs to be a separate
table to make up the composition of the "splits". What if you need a 3 way
split? Or more? With a separate table, you could split things as far as
needed.

In any case, to avoid user entries, you should have a query that reports any
splits that do not add up to 100%. That would be a very good idea indeed!

Tom Ellison
 
Back
Top