tabs, relationships and records in forms

John, would it help if I saved my database and sent you a link on a file
sharing website or is that just too much? I just thought it might be simpler
if you look at it ... or not?

Cathy

"John W. Vinson" wrote:

On Mon, 15 Jun 2009 19:30:08 -0700, Cathydal
wrote:

Thanks so much for taking the time and interest John.

I am reading through some of the references and it makes sense, though I am
now a little overwhelmed with all the possibilities.

So Am I correct in saying that I need to split the master table so that each
child (with it's unique number) has it's own table with parents (yes there
are multiple types not just 2) and siblings are in that one table? If that
is true, then I would have at least 856 tables just with that data (as well
as their medical info and mailing group info). However, there will still be
repeat data as the child may have up to 5 siblings and 4 sick siblings so
gender/dob/dod will be repeated - so does that mean there needs to be more
breakdown of Child table - Sib 1 table - Sib 2 table (etc) - Sick Sib 1 table
- Sick sib 2 table (etc) - this would go on to what degree? I'm getting
more and more confused by the possibilities.

NO. I'm not saying that at all!

I'm suggesting that you could have *ONE TABLE* containing one record for each
person.

A child is a person. One record in this table.
A parent is a person. One record in this table.
A legal guardian is a person. One record in this table.

You'll have other tables - perhaps a table of Households, perhaps a table
indicating who is related to whom and how - but each individual Entity
(real-life person, thing, event or relationship) will be represented by *a
record in a table*.

Gina's suggestion involves separate tables for children and adults; that's a
very valid approach, especially if (for the purposes of your database)
children and adults are considered different kinds of entities. That's pretty
likely here, since you are interested in different information about a child
than about a parent.

As noted in this thread "normalization" is both a science and an art, and it's
not all that easy! Don't feel overwhelmed; reread the references (again,
Crystal's table design is a good place to start). I'm pretty busy today but
I'll mark your post with the long table design and try to get back to you in a
day or two with some specific suggestions.

--

John W. Vinson [MVP]

On Tue, 16 Jun 2009 23:48:01 -0700, Cathydal
wrote:

John, would it help if I saved my database and sent you a link on a file
sharing website or is that just too much? I just thought it might be simpler
if you look at it ... or not?

The query you posted upthread has enough information. I'll try to get a look
at it today.

--

John W. Vinson [MVP]

Hi John, I think I'm in serious trouble. I have saved a second version and
was trying to alter the format to create subforms on separate tabs, but the
subforms are driven by the main form so I'm totally lost now.

Even though all 3 groups are connected ie Master Family List, Hopsice Data
and Volunteer Data - I created 3 tabs as they should all be outputting thier
own sets of data not the total from the Master.

Now that I realise tabs have no 'power' or authority I tried doing it by
using subforms, but they are literally like datasheets and just present the
data from the main form.

I am supposed to present this as almost a finished product in 3 days and I
have no idea how to make it work.

If you have any advice I would really appreciate it.

Thanks Cathy

"John W. Vinson" wrote:

On Tue, 16 Jun 2009 23:48:01 -0700, Cathydal
wrote:

John, would it help if I saved my database and sent you a link on a file
sharing website or is that just too much? I just thought it might be simpler
if you look at it ... or not?

The query you posted upthread has enough information. I'll try to get a look
at it today.

--

John W. Vinson [MVP]

On Sat, 20 Jun 2009 04:06:01 -0700, Cathydal
wrote:

I am supposed to present this as almost a finished product in 3 days and I
have no idea how to make it work.


I would be hard pressed working full time as an experienced Access developer
to get this non-normalized raw data into a properly normalized working
database in that time. If you need working forms and reports, I'd want a week.
Sorry, but what you're trying to do is probably simply unreasonable!

This is a *complex database*. Access is not as simple as Word or Excel (which
can themselves be far more complex than some pointy-haired-bosses imagine).

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


--

John W. Vinson [MVP]

Hi John,

Not only have I delivered a 'product' today, but I have learnt a hell of a
lot along the way. Thanks so much for your generous support and with Ken and
Gina's help I have put something (albeit not as slick as a programmer would
probably like) together that is a main form with 2 subforms (unbound to the
main form) so they each present their own data. I have successfully broken
down my larger table and am now working on tidying up a few things.

You would'nt happen to know if a union all select query works with more than
2 tables would you as I seem to be having a bit of trouble making mine work
.... unless of course there is another way to add the records of 3 or more
tables together to forma report.

Thanks again.

Cathy

"John W. Vinson" wrote:

On Sat, 20 Jun 2009 04:06:01 -0700, Cathydal
wrote:

I am supposed to present this as almost a finished product in 3 days and I
have no idea how to make it work.


I would be hard pressed working full time as an experienced Access developer
to get this non-normalized raw data into a properly normalized working
database in that time. If you need working forms and reports, I'd want a week.
Sorry, but what you're trying to do is probably simply unreasonable!

This is a *complex database*. Access is not as simple as Word or Excel (which
can themselves be far more complex than some pointy-haired-bosses imagine).

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


--

John W. Vinson [MVP]

Cathy,

You can more than two tables, however, bear in mind when doing a Union query
you must selct the same number of fields from each table. If you have more
fields in one table then the other, you can use NULL as a column 'holder'...
see sample below. While I used queries here the same applies to tables.

SELECT "Coils" AS ItemType, crCustomerID, crCoilID, crCoilListNet
FROM qryCoilsReceived
UNION ALL SELECT "Scrap", crCustomerID, crCoilID, NULL
FROM qryCoilsInStorage
UNION ALL SELECT "Material", mrCustomerID, mrMaterialID, mrNetWeight
FROM qryMaterialInStorage
UNION ALL SELECT "Lifts", oCustomerID, LiftStr, lGrossWeight
FROM qryOpenLifts;

Perhaps if you explain the trouble you are having with Union queries????

--
Gina Whipp

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

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

"Cathydal" wrote in message
...
Hi John,

Not only have I delivered a 'product' today, but I have learnt a hell of a
lot along the way. Thanks so much for your generous support and with Ken
and
Gina's help I have put something (albeit not as slick as a programmer
would
probably like) together that is a main form with 2 subforms (unbound to
the
main form) so they each present their own data. I have successfully
broken
down my larger table and am now working on tidying up a few things.

You would'nt happen to know if a union all select query works with more
than
2 tables would you as I seem to be having a bit of trouble making mine
work
... unless of course there is another way to add the records of 3 or more
tables together to forma report.

Thanks again.

Cathy

"John W. Vinson" wrote:

On Sat, 20 Jun 2009 04:06:01 -0700, Cathydal
wrote:

I am supposed to present this as almost a finished product in 3 days and
I
have no idea how to make it work.


I would be hard pressed working full time as an experienced Access
developer
to get this non-normalized raw data into a properly normalized working
database in that time. If you need working forms and reports, I'd want a
week.
Sorry, but what you're trying to do is probably simply unreasonable!

This is a *complex database*. Access is not as simple as Word or Excel
(which
can themselves be far more complex than some pointy-haired-bosses
imagine).

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


--

John W. Vinson [MVP]

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


"Gina Whipp" wrote:

Cathy,

You can more than two tables, however, bear in mind when doing a Union query
you must selct the same number of fields from each table. If you have more
fields in one table then the other, you can use NULL as a column 'holder'...
see sample below. While I used queries here the same applies to tables.

SELECT "Coils" AS ItemType, crCustomerID, crCoilID, crCoilListNet
FROM qryCoilsReceived
UNION ALL SELECT "Scrap", crCustomerID, crCoilID, NULL
FROM qryCoilsInStorage
UNION ALL SELECT "Material", mrCustomerID, mrMaterialID, mrNetWeight
FROM qryMaterialInStorage
UNION ALL SELECT "Lifts", oCustomerID, LiftStr, lGrossWeight
FROM qryOpenLifts;

Perhaps if you explain the trouble you are having with Union queries????

--
Gina Whipp

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

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

"Cathydal" wrote in message
...
Hi John,

Not only have I delivered a 'product' today, but I have learnt a hell of a
lot along the way. Thanks so much for your generous support and with Ken
and
Gina's help I have put something (albeit not as slick as a programmer
would
probably like) together that is a main form with 2 subforms (unbound to
the
main form) so they each present their own data. I have successfully
broken
down my larger table and am now working on tidying up a few things.

You would'nt happen to know if a union all select query works with more
than
2 tables would you as I seem to be having a bit of trouble making mine
work
... unless of course there is another way to add the records of 3 or more
tables together to forma report.

Thanks again.

Cathy

"John W. Vinson" wrote:

On Sat, 20 Jun 2009 04:06:01 -0700, Cathydal
wrote:

I am supposed to present this as almost a finished product in 3 days and
I
have no idea how to make it work.


I would be hard pressed working full time as an experienced Access
developer
to get this non-normalized raw data into a properly normalized working
database in that time. If you need working forms and reports, I'd want a
week.
Sorry, but what you're trying to do is probably simply unreasonable!

This is a *complex database*. Access is not as simple as Word or Excel
(which
can themselves be far more complex than some pointy-haired-bosses
imagine).

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


--

John W. Vinson [MVP]

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

Cathydal wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200906/1

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" wrote:

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

Cathydal wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200906/1

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

Cathydal wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200906/1