tabs, relationships and records in forms

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 :)

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]
I wish you luck, but this Wednesday is just too soon.
 
C

Cathydal

Yeah I worked that out. Lucky I work in a lot of copy and paste so I worked
it out and worked around it. Ken do you think this is the best/easiest
option to combine these types of data as I can see the need to do this for a
number of my reports.

Cathy

KenSheridan via AccessMonster.com said:
I see that I somehow managed to put "Pending" instead of "ANP", along with an
extra quotes character in the last part of the UNION ALL operation. I hope
it didn't confuse you too much.

Ken Sheridan
Stafford, England
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 :)
[quoted text clipped - 77 lines]
I wish you luck, but this Wednesday is just too soon.
 
C

Cathydal

Ken, I seem to have gotten into further strife! It seems that I cannot
update or edit my data, either through the form view or using tables - well I
can add to the tables but none of the reports or queries are being modifed.
From my research it seems to be the fault of union queries.

If that is the case then this becomes a static database. A - is this
correct? and B have you any suggestions on how to resolve this?

Thanks ... desperately ... Cathy
 
G

Gina Whipp

Cathydal,

I am going to restate what we have been saying along. You really need to
consider 'redoing' your tables. An example of what happens when a database
is not properly normalized is what you are experiencing now. As Ken said,
we all know this may seem overwhelming but in the long run writing queries,
creating forms for data entry and creating reports will be far easier and
well worth the time.

Access is unlike Excel or Word, it's not a learn as you go program. It has
a steep learning curve which starts with understanding relational design.
Here are some resources...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Think of it like building a house. You can't go hang dry wall if the
foundation hasn't been laid; you can't hang light fixtures until the wiring
has been put in... You are attempting to hang light fixtures the foundation
is a bit wobbly! You need to go back to the tables (your foundation) and
get them stable.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top