tabs, relationships and records in forms

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

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

Cathy,

[quoted text clipped - 55 lines]

I wish you luck, but this Wednesday is just too soon.

 

[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

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

Cathy:

[quoted text clipped - 77 lines]

I wish you luck, but this Wednesday is just too soon.

 

[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

 

[Gina Whipp]

Cathydal,

Union queries are uneditable. They are great for reports but for forms,
unless you are just displaying the data, they do not work for forms OR
trying to edit the data in the query.

--
Gina Whipp

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

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

 

[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

 

[Gina Whipp]

Ken,

Now that I finished laughing! Thank you...

I only reinforced what you AND John have already said... sometimes, the
third time is a charm!

--
Gina Whipp

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

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