Howdy to you, too, also from Oklahoma!
Even though you may already have taken care of your immediate
problem, what I'm about to suggest may save lots of work later. The
basic idea (as Dorian pointed out) is to avoid having to maintain
several fields that contain the same kinds of stuff (in your case, a
product number). For example, suppose you wanted to determine the last
product number in alphabetical order? Ignoring that it might be a silly
thing to want to do, you'd have a hard time doing it if they're
scattered around among several fields.
I'm going to suggest here setting up some preliminary Queries on
which to base your Union Query. You may actually prefer Dorian's
version (less writing), but doing it this way makes it easier to edit
your Queries later, since you can't edit a Union Query in Query Design View.
We start with your Table:
[Mytable] Table Datasheet View:
RecordID Prodno1 Prodno2 Prodno3 Prodno4 Sum1
-------- ------- ------- ------- ------- ----
10001 A123 12
10002 B789 10
10003 A123 8
10004 B789 14
.... and use a Select Query to pull out just the first of the four
[Prodno] fields. I renamed [RecordID] to [RedNum] because I like to use
the tag "ID" for unique record keys, and the results will not be
guaranteed to be unique. For example, two [Prodno] fields in one record
might have non-null values. Maybe you don't intend them to, but you
don't want your Query to break if that does happen. (You can always
write a Query that lists any duplicates, so that you can figure out how
to correct the problem.)
The [Field] field identifies from which one of the original four
[Prodno] fields a particular record comes.
[Q_01Field1] SQL:
SELECT RecordID AS RecNum, 1 AS Field,
Prodno1 AS ProdNo, Sum1
FROM Mytable
WHERE Prodno1 Is Not Null
ORDER BY RecordID;
[Q_01Field1] Query Datasheet View:
RecNum Field ProdNo Sum1
------ ----- ------ ----
10001 1 A123 12
10004 1 B789 14
For the second [Prodno] field, I defined a similar Query. However,
since Union Queries don't care about field names except for the first
Query they include, I didn't rename any of the fields in this one.
[Q_01Field2] SQL:
SELECT [RecordID], 2 AS Field,
[Prodno2], [Sum1]
FROM Mytable
WHERE Prodno2 Is Not Null;
[Q_01Field2] Query Datasheet View:
RecordID Field Prodno2 Sum1
-------- ----- ------- ----
10002 2 B789 10
[Q_01Field3] and [Q_01Field4] are almost identical to [Q_01Field2],
except that each 2 in the SQL is changed to a 3 or 4.
Having defined the 4 Queries (which are editable in Query Design View),
we can combine them into the following Union Query (pretty much the same
as Dorian's version, except that it's based on other Queries instead of
directly on the [Mytable] Table).
In a Union Query, the ORDER BY (as well as the Query itself) uses field
names from its first SELECT, which in this case is [Q_01Field1].
[QU_02ProductSums] SQL:
SELECT * FROM Q_01Field1
UNION ALL
SELECT * FROM Q_01Field2
UNION ALL
SELECT * FROM Q_01Field3
UNION ALL
SELECT * FROM Q_01Field4
ORDER BY RecNum, Field;
The results look like this:
[QU_02ProductSums] Query Datasheet View:
RecNum Field ProdNo Sum1
------ ----- ------ ----
10001 1 A123 12
10002 2 B789 10
10003 4 A123 8
10004 1 B789 14
My following comments may not apply to you; it depends on if you have
control over Table [Mytable]. If you don't (for example, if you are
linking to a Table in another database, or if you are importing data
from a spreadsheet that someone else maintains), then you will probably
want to use [QU_02ProductSums] or something similar as the basis for
your other calculations.
If you have the option of revising your Table design, I think you'll
find all sorts of later work a lot easier if you create a new Table
based on [QU_02ProductSums] -- and if you do that, you can get rid of
[QU_02ProductSums] and the Queries it depends on. To do that, you can
use the following Make-Table Query:
[QM_03ProductSums] SQL:
SELECT * INTO T_ProductSums
FROM QU_02ProductSums;
The results will look a lot like the output from [QU_02ProductSums], but
being a Table, it will allow you to edit the results, such as
appending records to it.
[T_ProductSums] Table Datasheet View:
RecNum Field ProdNo Sum1
------ ----- ------ ----
10001 1 A123 12
10002 2 B789 10
10003 4 A123 8
10004 1 B789 14
As I mentioned, if you are allowed to replace [Mytable] with
[T_ProductSums], I suggest doing so, but if not, use [QU_02ProductSums]
(or the equivalent). To use the Union Query instead of the Table,
change the "FROM" clause of the following SQL to
"FROM QU_02ProductSums".
You may hide the [MinOfRecNum] field in Query Datasheet View, but it
must be left visible in Query Design View to be usable in the
[NewRecNum] field.
[Q_04Totals] SQL:
SELECT MinOfRecNum+10000 AS NewRecNum, ProdNo,
Sum([Sum1]) AS Total, Min(RecNum) AS MinOfRecNum
FROM T_ProductSums
GROUP BY ProdNo
ORDER BY ProdNo;
[Q_04Totals] Query Datasheet View:
NewRecNum ProdNo Total MinOfRecNum
--------- ------ ----- -----------
20001 A123 20 10001
20002 B789 24 10002
I would have suggested leaving out the [Field] field, since you
don't actually ever use it in the [Q_04Totals] Quey, and especially
since your example shows "B789" as [Prodno2] in one record and as
[Prodno1] in another record, so my guess is that it's not important to
you. But it's easier to include it and not need it than to add it
later, just in case you eventually have some need for it. (Especially
if you toss out the original Table and use only the replacement.)
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
