Combining 3 Fields into 1 in query results

[Anonymous]

Hello again to the Access experts out there! I have a table with 7 fields.
They are:

IndexID, Date1, Date1Qty, Date2, Date2Qty, Date3, Date3Qty

I'm essentially trying to make a query that ends up with 3 fields - IndexID,
Date, Qty. The trick is that I need the query to combine all the date
fields into a single date field result set and the associated Qty fields
into a single Qty field. Then, I need to order the whole thing descending
by date. So if my table contains the following data:

0, 7/7/05, 900, 7/10/05,800, 7/13/05, 500
1, 7/12/05,300,null,null,null,null
2, 7/6/05, 200, 7/11/05, 40, null, null

I need this:

0, 7/13/05, 500
1, 7/12/05, 300
2, 7/11/05, 40
0, 7/10/05, 800
0, 7/7/05, 900
2, 7/6/05, 200

Can I do this with a Union Query? Seems like I can't because the field
names are different (date1, date2, date3) and (date1qty, date2qty,
date3qty). As always, any help would be GREATLY appreciated! Happy
Thanksgiving!

 

[Anonymous]

I Figured it out with a little experimentation of my own. Here's the query:

select indexID, date1 as [Date], date1Qty as [Qty] from tTest where date1 is
not null
union
select indexID, date2 as [Date], date2Qty as [Qty] from tTest where date2 is
not null
union
select indexID, date3 as [Date], date3Qty as [Qty] from tTest where date3 is
not null
order by date desc



However, here's another question. Anyone know how to throw a subtotal on
the bottom of the Qty column to show how many total there were? This would
be useful in lots of queries that I have in access, but I don't know how to
put a subtotal in (either across a row or down a column).

Many Thanks!

 

[John Vinson]

Hello again to the Access experts out there! I have a table with 7 fields.
They are:

IndexID, Date1, Date1Qty, Date2, Date2Qty, Date3, Date3Qty

Can I do this with a Union Query? Seems like I can't because the field
names are different (date1, date2, date3) and (date1qty, date2qty,
date3qty). As always, any help would be GREATLY appreciated! Happy
Thanksgiving!

The field names are irrelevant; they don't NEED to be the same. The
names of the fields in the UNION query will be the names in the first
SELECT statement (either the actual fieldname or the alias you
provide), and the sort order will be that specified in the last
SELECT:

SELECT IndexID, Date1 AS TheDate, Date1Qty AS Qty FROM table
WHERE Date1Qty IS NOT NULL
UNION ALL
SELECT IndexID, Date2, Date2Qty FROM table
WHERE Date2Qty IS NOT NULL
UNION ALL
SELECT IndexID, Date3, Date3Qty FROM table
WHERE Date3Qty IS NOT NULL
ORDER BY TheDate;

will do the job for you.

Suggestion - base a MakeTable query on this, and junk your
non-normalized spreadsheet!

John W. Vinson[MVP]

 

[John Vinson]

However, here's another question. Anyone know how to throw a subtotal on
the bottom of the Qty column to show how many total there were? This would
be useful in lots of queries that I have in access, but I don't know how to
put a subtotal in (either across a row or down a column).

YOu can't, and shouldn't.

Queries (like tables) are "homogenous" - all records are of the same
"level". You can't have some records that are detail records, and
other records that are summaries of detail records. (Well, you *can*,
by UNIONing a table and a Totals query, but generally you shouldn't).

Instead, use tables to store your data; queries to aggreagate it; and
Forms and Reports to present it. You can put subtotals on the Footer
of forms or reports by putting a textbox

=Sum([qty])

or

=Count(*)


John W. Vinson[MVP]

 

[Anonymous]

Thanks John!

Actually, you answered my question perfectly. I really wanted the total to
appear on the bottom of some reports I had created. Fantastic! Thanks a
bunch!

-Michael

However, here's another question. Anyone know how to throw a subtotal on
the bottom of the Qty column to show how many total there were? This
would
be useful in lots of queries that I have in access, but I don't know how
to
put a subtotal in (either across a row or down a column).

YOu can't, and shouldn't.

Queries (like tables) are "homogenous" - all records are of the same
"level". You can't have some records that are detail records, and
other records that are summaries of detail records. (Well, you *can*,
by UNIONing a table and a Totals query, but generally you shouldn't).

Instead, use tables to store your data; queries to aggreagate it; and
Forms and Reports to present it. You can put subtotals on the Footer
of forms or reports by putting a textbox

=Sum([qty])

or

=Count(*)


John W. Vinson[MVP]