C
Cathydal
Yay, thanks Ken, after a bit of shuffling, it worked! It's all taking shape.
Cathy (Sorry I haven't responded to your email yet, have had my head down.)
C
Cathy (Sorry I haven't responded to your email yet, have had my head down.)
C
KenSheridan via AccessMonster.com said:Cathy:
It looks like your problem stems from a design flaw in that you are 'encoding
data' both as table names and as column headings, whereas a fundamental
principle of the database relational model (the Information Principle) is
that data is stored as values at row positions in tables and in no other way.
You can possibly work around it in this case by using constants as the final
column:
SELECT [Vol Master List 15-5-09].vfirstname, [Vol Master List
15-5-09].vsurname, [Vol Master List 15-5-09].[VOL SAAP No], [Vol Master List
15-5-09].vCITY, [Vol Master List 15-5-09].vhphone, "Resting" AS Status
FROM [Vol Master List 15-5-09]
WHERE ((([Vol Master List 15-5-09].Resting) Is Not Null))
UNION ALL
SELECT [Vol Master List 15-5-09].vfirstname, [Vol Master List
15-5-09].vsurname, [Vol Master List 15-5-09].[VOL SAAP No], [Vol Master List
15-5-09].vCITY, [Vol Master List 15-5-09].vhphone, "Pending"
FROM [Vol Master List 15-5-09]
WHERE ((([Vol Master List 15-5-09].[Lind Pendg]) Is Not Null))
UNION ALL
SELECT [Vol Master List 15-5-09].vfirstname, [Vol Master List
15-5-09].vsurname, [Vol Master List 15-5-09].[VOL SAAP No], [Vol Master List
15-5-09].vCITY, [Vol Master List 15-5-09].vhphone, "
"Pending"
FROM [Vol Master List 15-5-09]
WHERE ((([Vol Master List 15-5-09].ANP) Is Not Null));
This would place the constants "Resting", "Pending" or "ANP" in a column
named Status on which you could then group the report. You can of course
change the column name to something more suitable by amending the SQL for the
first part of the UNION ALL operation.
A more correct design would have been to have just one table with a column
Status, or have this column in a separate related table if a row in the main
table have more than one 'status' value.
Ken Sheridan
Stafford, England
Thanks Gina,
The problem is I have 3 tables (resting - 42 records), (Link Pending - 3
records) and ANP (9 records) with the same types of fields only the final
field is different and I want to add the three tables together and show the
grouping of the final field.
SELECT [Vol Master List 15-5-09].vfirstname, [Vol Master List
15-5-09].vsurname, [Vol Master List 15-5-09].[VOL SAAP No], [Vol Master List
15-5-09].vCITY, [Vol Master List 15-5-09].vhphone, [Vol Master List
15-5-09].Resting
FROM [Vol Master List 15-5-09]
WHERE ((([Vol Master List 15-5-09].Resting) Is Not Null));
UNION ALL SELECT [Vol Master List 15-5-09].vfirstname, [Vol Master List
15-5-09].vsurname, [Vol Master List 15-5-09].[VOL SAAP No], [Vol Master List
15-5-09].vCITY, [Vol Master List 15-5-09].vhphone, [Vol Master List
15-5-09].[Lind Pendg]
FROM [Vol Master List 15-5-09]
WHERE ((([Vol Master List 15-5-09].[Lind Pendg]) Is Not Null));
UNION ALL SELECT [Vol Master List 15-5-09].vfirstname, [Vol Master List
15-5-09].vsurname, [Vol Master List 15-5-09].[VOL SAAP No], [Vol Master List
15-5-09].vCITY, [Vol Master List 15-5-09].vhphone, [Vol Master List
15-5-09].ANP
FROM [Vol Master List 15-5-09]
WHERE ((([Vol Master List 15-5-09].ANP) Is Not Null));
So the query works ie I get the total of 54 records however it all comes
into one field "resting". Should I add 2 extra fields and as you suggested
put in Null as the column holder so that each is placed in a different field?
or how do I combine the three so that I can group them later?
Cathy
[quoted text clipped - 55 lines]Cathy,I wish you luck, but this Wednesday is just too soon.