tabs, relationships and records in forms

C

Cathydal

Hi I am a new builder and have built an Access 2003 database with 3 major
tables. I have set up 3 tabs to represent the 3 areas of business, but am
having trouble viewing the correct number of records.

The 1st tab should have over 800 records and the other two tabs draw from
this tables fields.

However untill I altered the relationships and the joins of the tables I
could only access the same number of records (34) in all three tabs.

Now I have altered the joins but I see blank records in the two smaller
tabs. I'm not sure if I have gone about this all wrong, but I'm too far in
to rebuild. Please help
 
T

tina

well, it's impossible to help you when you've provided virtually no
information. we can't see your database, or anything in it, so you have to
describe everything to us. what are the names of the tables, and the fields
in each, and how are they related to each other? what business process are
you supporting with the database? tabs don't have records, or any way to
display records directly; do you have a mainform with 2 subforms on it? or
an unbound mainform, with 3 subforms in it? if bound, what table or query is
the mainform bound to? if a query, what tables are included in the query?
ditto those questions for each subform. what records do you want to see, and
what records are you seeing?

hth
 
C

Cathydal

Thank you Tina, Steve and Ken. You are quite right Tina. As I said I am
still on 'L' Plates so I should have given more inforamtion but as I am self
taught I didn't know how much to give etc.

Anyway - It is essentiatlly a contact list for covering 3 distinct areas -
ie families, hospice and volunteers so I have set up the form with 2 other
tabs, but No T I have not got any subforms. The Master List table has 846
records with about 65 fields, the Hospice table has 178 records with 20
fields and the Vol table has 174 records with 42 fields. It is essentially a
mailing list and contacts database. I have now managed to view all records
only its more than the master list (it's now showing 846) so it has picked up
some from the Vol list.

The three tables are all bound to to form and I have set up the master list
as the main table with a left join to the hospice and a right join to the
vols with a select query
SELECT [Master Family List 2-6-09].Unique, [Master Family List 2-6-09].[ID
No], [Master Family List 2-6-09].Region, [Master Family List 2-6-09].[Key
Contact], [Master Family List 2-6-09].[Child's Surname], [Master Family List
2-6-09].[Child's First Name], [Master Family List 2-6-09].Gender, [Master
Family List 2-6-09].DOB, [Master Family List 2-6-09].DOD, [Master Family List
2-6-09].[Condition 1], [Master Family List 2-6-09].[Condition 2], [Master
Family List 2-6-09].[Condition 3], [Master Family List 2-6-09].[Malignant
Condition], [Master Family List 2-6-09].[Hospice Use], [Master Family List
2-6-09].[Other Deceased Children], [Master Family List 2-6-09].[Bereaved
Family], [Master Family List 2-6-09].[Parent's Surname 1], [Master Family
List 2-6-09].[Parent's First Name 1], [Master Family List 2-6-09].[Gender 2],
[Master Family List 2-6-09].Code, [Master Family List 2-6-09].[Address 1],
[Master Family List 2-6-09].[Suburb 1], [Master Family List 2-6-09].[Postcode
1], [Master Family List 2-6-09].State, [Master Family List 2-6-09].[Home
Phone 1], [Master Family List 2-6-09].[Work Phone 1], [Master Family List
2-6-09].[Fax 1], [Master Family List 2-6-09].[Mobile 1], [Master Family List
2-6-09].[Email 1], [Master Family List 2-6-09].[Parent's Surname 2], [Master
Family List 2-6-09].[Parent's First Name 2], [Master Family List
2-6-09].[Gender 3], [Master Family List 2-6-09].[Address 2], [Master Family
List 2-6-09].[Suburb 2], [Master Family List 2-6-09].[Postcode 2], [Master
Family List 2-6-09].[State 2], [Master Family List 2-6-09].[Home Phone 2],
[Master Family List 2-6-09].[Work Phone 2], [Master Family List
2-6-09].[Mobile 2], [Master Family List 2-6-09].[Email 2], [Master Family
List 2-6-09].[Salutation 1], [Master Family List 2-6-09].[Salutation 2],
[Master Family List 2-6-09].[Sibling Surname], [Master Family List
2-6-09].[Sibling Name 1], [Master Family List 2-6-09].[Gender 4], [Master
Family List 2-6-09].[DOB 2], [Master Family List 2-6-09].[Sibling Name 2],
[Master Family List 2-6-09].[Gender 5], [Master Family List 2-6-09].[DOB 3],
[Master Family List 2-6-09].[Sibling Name 3], [Master Family List
2-6-09].[Gender 6], [Master Family List 2-6-09].[DOB 4], [Master Family List
2-6-09].[Sibling Name 4], [Master Family List 2-6-09].[Gender 7], [Master
Family List 2-6-09].[DOB 5], [Master Family List 2-6-09].[Sibling Name 5],
[Master Family List 2-6-09].[Gender 8], [Master Family List 2-6-09].[DOB 6],
[Master Family List 2-6-09].[Sick Child 1 Surname], [Master Family List
2-6-09].[Sick Child 1 Name], [Master Family List 2-6-09].[Gender 9], [Master
Family List 2-6-09].[DOB 7], [Master Family List 2-6-09].[DOD 2], [Master
Family List 2-6-09].[Sick Child 2 Surname], [Master Family List 2-6-09].[Sick
Child 2 Name], [Master Family List 2-6-09].[Gender 10], [Master Family List
2-6-09].[DOB 8], [Master Family List 2-6-09].DOD3, [Master Family List
2-6-09].[Sick Child 3 Surname], [Master Family List 2-6-09].[Sick Child 3
Name], [Master Family List 2-6-09].[Gender 11], [Master Family List
2-6-09].[DOB 9], [Master Family List 2-6-09].DOD4, [Master Family List
2-6-09].[Sick Child 4 Surname], [Master Family List 2-6-09].[Sick Child 4
Name], [Master Family List 2-6-09].[Gender 12], [Master Family List
2-6-09].[DOB 10], [Master Family List 2-6-09].DOD5, [Master Family List
2-6-09].[SP Salutation], [Master Family List 2-6-09].[SP Salutation 2],
[Master Family List 2-6-09].[AP Salutation], [Master Family List 2-6-09].[BSP
Salutation], [Master Family List 2-6-09].[SP - ml], [Master Family List
2-6-09].[BSP - ml], [Master Family List 2-6-09].[AP - ml], [Master Family
List 2-6-09].CALD, [Master Family List 2-6-09].Links, [Master Family List
2-6-09].[Family Networking], [Master Family List 2-6-09].[Lord Somers Camp],
[Master Family List 2-6-09].[Dad's Day], [Master Family List 2-6-09].[Cont
Links], [Master Family List 2-6-09].[Anniversary Card], [Master Family List
2-6-09].[AC Salutation+], [Master Family List 2-6-09].[Creative Memories],
[Master Family List 2-6-09].[Bereaved Parent Weekend], [Master Family List
2-6-09].[Bereaved Family Networking], [Master Family List
2-6-09].[Remembrance Day], [Master Family List 2-6-09].[No Mail], [Hospice
Stats 08-09 14-5-09].[unique no], [Hospice Stats 08-09 14-5-09].[Stay No],
[Hospice Stats 08-09 14-5-09].[Date of Last Stay], [Hospice Stats 08-09
14-5-09].[Surname, Firstname], [Hospice Stats 08-09 14-5-09].[SAAP No],
[Hospice Stats 08-09 14-5-09].[New Family], [Hospice Stats 08-09
14-5-09].[CALD Famiy], [Hospice Stats 08-09 14-5-09].Diagnosis, [Hospice
Stats 08-09 14-5-09].Classifications, [Hospice Stats 08-09 14-5-09].[Medical
Needs], [Hospice Stats 08-09 14-5-09].[Care Needs], [Hospice Stats 08-09
14-5-09].[Types of Stays Utilised], [Hospice Stats 08-09 14-5-09].Deaths,
[Hospice Stats 08-09 14-5-09].[Short Notice Respite], [Hospice Stats 08-09
14-5-09].[Hospice Initiated Stays], [Hospice Stats 08-09 14-5-09].[Family
Initiated Stays], [Hospice Stats 08-09 14-5-09].[Emergency Type], [Hospice
Stats 08-09 14-5-09].[Emergency Stays], [Hospice Stats 08-09 14-5-09].[No of
Cancelled Days], [Hospice Stats 08-09 14-5-09].[Party Cancelling], [Hospice
Stats 08-09 14-5-09].[No of Funded Nights], [Hospice Stats 08-09
14-5-09].[Total Days In Hospice], [Vol Master List 15-5-09].[VOL SAAP No],
[Vol Master List 15-5-09].vfirstname, [Vol Master List 15-5-09].vsurname,
[Vol Master List 15-5-09].vstreet, [Vol Master List 15-5-09].vCITY, [Vol
Master List 15-5-09].vSTATE, [Vol Master List 15-5-09].[vP CODE], [Vol Master
List 15-5-09].vhphone, [Vol Master List 15-5-09].vwphone, [Vol Master List
15-5-09].vmobphone, [Vol Master List 15-5-09].vemail, [Vol Master List
15-5-09].[Lind Pendg], [Vol Master List 15-5-09].ANP, [Vol Master List
15-5-09].Resting, [Vol Master List 15-5-09].[Review Date (only if N)], [Vol
Master List 15-5-09].[Home Vol], [Vol Master List 15-5-09].[Family SAAP No],
[Vol Master List 15-5-09].[Family Surname], [Vol Master List 15-5-09].FSW,
[Vol Master List 15-5-09].RCH, [Vol Master List 15-5-09].Hospice, [Vol Master
List 15-5-09].[Rostered Day], [Vol Master List 15-5-09].[Activities Only],
[Vol Master List 15-5-09].SP, [Vol Master List 15-5-09].BSP, [Vol Master List
15-5-09].AP, [Vol Master List 15-5-09].MMC, [Vol Master List 15-5-09].[Rem
Day], [Vol Master List 15-5-09].[B Parent Weekend], [Vol Master List
15-5-09].[Creative M], [Vol Master List 15-5-09].[Coffee Mornings], [Vol
Master List 15-5-09].[Hosp Sch Hol], [Vol Master List 15-5-09].[Xmas Party],
[Vol Master List 15-5-09].[Reg Family Day], [Vol Master List 15-5-09].Admin,
[Vol Master List 15-5-09].Friends, [Vol Master List 15-5-09].[VTP Group No],
[Vol Master List 15-5-09].[VTP end date], [Vol Master List 15-5-09].[Police
Check], [Vol Master List 15-5-09].[WWC Expiry], [Vol Master List
15-5-09].[All Vols], [Vol Master List 15-5-09].[Continuing Links], [Vol
Master List 15-5-09].[No Mail]
FROM ([Master Family List 2-6-09] LEFT JOIN [Hospice Stats 08-09 14-5-09] ON
[Master Family List 2-6-09].[ID No] = [Hospice Stats 08-09 14-5-09].[SAAP
No]) LEFT JOIN [Vol Master List 15-5-09] ON [Master Family List 2-6-09].[ID
No] = [Vol Master List 15-5-09].[Family SAAP No]
ORDER BY [Master Family List 2-6-09].[Child's Surname];

I have also written numerous action queries to produce reports/mailing
labels for each of the 3 groups. They are working fine.

My problem is to alter the 1st query to link the 3 tables but not mutually
exclusively - likewise not all encompassing. I'm also not sure using the
some of the same field on the 3 different tabs - will this be causing the
tabs to show ALL 855 records instead of the smaller number?

Thanks Cathydal
 
J

John W. Vinson

Hi I am a new builder and have built an Access 2003 database with 3 major
tables. I have set up 3 tabs to represent the 3 areas of business, but am
having trouble viewing the correct number of records.

The 1st tab should have over 800 records and the other two tabs draw from
this tables fields.

However untill I altered the relationships and the joins of the tables I
could only access the same number of records (34) in all three tabs.

Now I have altered the joins but I see blank records in the two smaller
tabs. I'm not sure if I have gone about this all wrong, but I'm too far in
to rebuild. Please help
Click to expand...

A Tab is *NOTHING* but a tool to manage screen space.

A Tab doesn't have any records.
A Tab doesn't store any data.

You can display data from a Query on a form with or without using tab pages,
but the problems you're having aren't related to the tab pages, but rather to
the Query you're using... which you have not described for us.

What are the three Tables?
What is each table's Primary Key? (If that term isn't familiar or the table
doesn't have one, you need to do some work...)
How are the tables related?
What Query are you using to display the data on the "tab"?
 
C

Cathydal

Hi Ken,

I see where you are going with this but first I must explain some things.
The multiples are because in a child's family there can be more than one sick
sibling (up to 4) and more than one sibling (up to 5) and 2 parents so they
each have a gender and the siblings each have a date of birth (some having a
date of death).

All children in the Master file (Family contacts) will be in the Hospice
file and in the Volunteer file, however, not all volunteers are connected
with a family so don't always connect to the master file.

How would I separate the tables to get a contact file? And how would I
separate the siblings (other than manually of course)? As there are so many
fields I would n't know where to start as there is nothing linking each of
the three. That is why I have added uniques numbers to the master and
hospice tables.

I'm pretty sure using the tabs, but making each tab a sub-form is the way to
go, but I'm a bit stuck on how to separate my data into separate tables to be
workable. I almost did this with analyser the other day then got scared.
How could I trust it to make the correct choices?

Thanks Cathydal

KenSheridan via AccessMonster.com said:
By the sound of it what you have here is type/sub-types scenario, a contacts
Type and three sub-types FamilyContacts, HospiceContacts and
VolunteerContacts. These are sometimes also referred to as class and sub-
class.

Firstly remember that in a relational database a table models an 'entity
type'. When something is an instance sub-type of a (super) type it means
that it is a member of both entity types. Everyone is a member of the
Contacts entity type, but not all contacts are members of the FamilyContacts
entity type, as they might be members of the HospiceContacts or
VolunteerContacts entity type. A member of a sub-type is always s a member
of its (super) type, but might or might not be a member of one or more other
sub-types, e.g. a family contact could conceivably also be a volunteer
contact and/or a hospice contact.

The columns (fields) of a table represent 'attributes' of the entity type
which the table is modelling. Contacts (super type) has those attributes
which are common to all contacts, name and address data etc., so a Contacts
table will have columns for these. Each sub-type has attributes specific to
itself, so the FamilyContacts, HospiceContacts and VolunteerContacts tables
will each have a different set of columns representing those attributes which
apply to the sub-type in question, but not to the other sub-types. You'll be
able to determine better than us what these are.

To tie all the tables the Contacts table is related to each of the three sub-
types tables in a one-to-one relationship. This is one of the few situations
in which one-to-one relationships are used. This is done by the primary key
column of each sub-type table also being a foreign key column which
references the primary key of the Contacts table. So all the tables can have
a ContactID column as their primary key. In the case of the Contacts table
this can be an autonumber, but in the case of the FamilyContacts,
HospiceContacts and VolunteerContacts tables it must be a straightforward
long integer data type column, not an autonumber.

For data entry of each type of contact don't try doing everything in one huge
query. Create three separate queries, one which joins Contacts to one of the
thee sub-type tables. In each include the ContactID column from the
Contacts table only, the correct value will automatically be inserted in the
ContactID column in the sub-type table when you add a new record. You can
either have a separate form bound to each query and open it from another form,
e.g. a switchboard when you want to add or edit a specific contact type, or
you can put each as a subform on a separate page of a tab control in one
unbound form.

If you want to see all contacts together, including the data from the three
sub-types tables then you can join all four tables in one query, but you will
have to LEFT JOIN contacts to each of the others as otherwise the query would
only return those contacts who were FamilyContacts, HospiceContacts and
VolunteerContacts, which is probably not many I'd guess.

The above deals with the basic framework, but I notice that you seem to be
using multiple columns for some attributes, e.g. Siblings. This is not good
design. The way a relational database models that sort of thing would be to
either to have a separate Siblings table related to the FamilyContacts (I'd
guess) table on ContactID, with one row per sibling; or if all siblings are
recorded as contacts in their own right, by having another table with columns
such as SiblingTo and SiblingOf, each referencing the primary key of
FamilyContacts, this enabling multiple joins between different rows in the
table.

I see you also appear to have multiple Gender columns. I may have lived a
sheltered life, but in my experience people have generally been one gender
only, at least at any one time; excluding the occasional hermaphrodite
perhaps, but even they don't usually manage more than two genders
simultaneously. Similarly, why multiple DoB columns? Reincarnation?

Ken Sheridan
Stafford, England
Thank you Tina, Steve and Ken. You are quite right Tina. As I said I am
still on 'L' Plates so I should have given more inforamtion but as I am self
taught I didn't know how much to give etc.

Anyway - It is essentiatlly a contact list for covering 3 distinct areas -
ie families, hospice and volunteers so I have set up the form with 2 other
tabs, but No T I have not got any subforms. The Master List table has 846
records with about 65 fields, the Hospice table has 178 records with 20
fields and the Vol table has 174 records with 42 fields. It is essentially a
mailing list and contacts database. I have now managed to view all records
only its more than the master list (it's now showing 846) so it has picked up
some from the Vol list.

The three tables are all bound to to form and I have set up the master list
as the main table with a left join to the hospice and a right join to the
vols with a select query
SELECT [Master Family List 2-6-09].Unique, [Master Family List 2-6-09].[ID
No], [Master Family List 2-6-09].Region, [Master Family List 2-6-09].[Key
Contact], [Master Family List 2-6-09].[Child's Surname], [Master Family List
2-6-09].[Child's First Name], [Master Family List 2-6-09].Gender, [Master
Family List 2-6-09].DOB, [Master Family List 2-6-09].DOD, [Master Family List
2-6-09].[Condition 1], [Master Family List 2-6-09].[Condition 2], [Master
Family List 2-6-09].[Condition 3], [Master Family List 2-6-09].[Malignant
Condition], [Master Family List 2-6-09].[Hospice Use], [Master Family List
2-6-09].[Other Deceased Children], [Master Family List 2-6-09].[Bereaved
Family], [Master Family List 2-6-09].[Parent's Surname 1], [Master Family
List 2-6-09].[Parent's First Name 1], [Master Family List 2-6-09].[Gender 2],
[Master Family List 2-6-09].Code, [Master Family List 2-6-09].[Address 1],
[Master Family List 2-6-09].[Suburb 1], [Master Family List 2-6-09].[Postcode
1], [Master Family List 2-6-09].State, [Master Family List 2-6-09].[Home
Phone 1], [Master Family List 2-6-09].[Work Phone 1], [Master Family List
2-6-09].[Fax 1], [Master Family List 2-6-09].[Mobile 1], [Master Family List
2-6-09].[Email 1], [Master Family List 2-6-09].[Parent's Surname 2], [Master
Family List 2-6-09].[Parent's First Name 2], [Master Family List
2-6-09].[Gender 3], [Master Family List 2-6-09].[Address 2], [Master Family
List 2-6-09].[Suburb 2], [Master Family List 2-6-09].[Postcode 2], [Master
Family List 2-6-09].[State 2], [Master Family List 2-6-09].[Home Phone 2],
[Master Family List 2-6-09].[Work Phone 2], [Master Family List
2-6-09].[Mobile 2], [Master Family List 2-6-09].[Email 2], [Master Family
List 2-6-09].[Salutation 1], [Master Family List 2-6-09].[Salutation 2],
[Master Family List 2-6-09].[Sibling Surname], [Master Family List
2-6-09].[Sibling Name 1], [Master Family List 2-6-09].[Gender 4], [Master
Family List 2-6-09].[DOB 2], [Master Family List 2-6-09].[Sibling Name 2],
[Master Family List 2-6-09].[Gender 5], [Master Family List 2-6-09].[DOB 3],
[Master Family List 2-6-09].[Sibling Name 3], [Master Family List
2-6-09].[Gender 6], [Master Family List 2-6-09].[DOB 4], [Master Family List
2-6-09].[Sibling Name 4], [Master Family List 2-6-09].[Gender 7], [Master
Family List 2-6-09].[DOB 5], [Master Family List 2-6-09].[Sibling Name 5],
[Master Family List 2-6-09].[Gender 8], [Master Family List 2-6-09].[DOB 6],
[Master Family List 2-6-09].[Sick Child 1 Surname], [Master Family List
2-6-09].[Sick Child 1 Name], [Master Family List 2-6-09].[Gender 9], [Master
Family List 2-6-09].[DOB 7], [Master Family List 2-6-09].[DOD 2], [Master
Family List 2-6-09].[Sick Child 2 Surname], [Master Family List 2-6-09].[Sick
Child 2 Name], [Master Family List 2-6-09].[Gender 10], [Master Family List
2-6-09].[DOB 8], [Master Family List 2-6-09].DOD3, [Master Family List
2-6-09].[Sick Child 3 Surname], [Master Family List 2-6-09].[Sick Child 3
Name], [Master Family List 2-6-09].[Gender 11], [Master Family List
2-6-09].[DOB 9], [Master Family List 2-6-09].DOD4, [Master Family List
2-6-09].[Sick Child 4 Surname], [Master Family List 2-6-09].[Sick Child 4
Name], [Master Family List 2-6-09].[Gender 12], [Master Family List
2-6-09].[DOB 10], [Master Family List 2-6-09].DOD5, [Master Family List
2-6-09].[SP Salutation], [Master Family List 2-6-09].[SP Salutation 2],
[Master Family List 2-6-09].[AP Salutation], [Master Family List 2-6-09].[BSP
Salutation], [Master Family List 2-6-09].[SP - ml], [Master Family List
2-6-09].[BSP - ml], [Master Family List 2-6-09].[AP - ml], [Master Family
List 2-6-09].CALD, [Master Family List 2-6-09].Links, [Master Family List
2-6-09].[Family Networking], [Master Family List 2-6-09].[Lord Somers Camp],
[Master Family List 2-6-09].[Dad's Day], [Master Family List 2-6-09].[Cont
Links], [Master Family List 2-6-09].[Anniversary Card], [Master Family List
2-6-09].[AC Salutation+], [Master Family List 2-6-09].[Creative Memories],
[Master Family List 2-6-09].[Bereaved Parent Weekend], [Master Family List
2-6-09].[Bereaved Family Networking], [Master Family List
2-6-09].[Remembrance Day], [Master Family List 2-6-09].[No Mail], [Hospice
Stats 08-09 14-5-09].[unique no], [Hospice Stats 08-09 14-5-09].[Stay No],
[Hospice Stats 08-09 14-5-09].[Date of Last Stay], [Hospice Stats 08-09
14-5-09].[Surname, Firstname], [Hospice Stats 08-09 14-5-09].[SAAP No],
[Hospice Stats 08-09 14-5-09].[New Family], [Hospice Stats 08-09
14-5-09].[CALD Famiy], [Hospice Stats 08-09 14-5-09].Diagnosis, [Hospice
Stats 08-09 14-5-09].Classifications, [Hospice Stats 08-09 14-5-09].[Medical
Needs], [Hospice Stats 08-09 14-5-09].[Care Needs], [Hospice Stats 08-09
14-5-09].[Types of Stays Utilised], [Hospice Stats 08-09 14-5-09].Deaths,
[Hospice Stats 08-09 14-5-09].[Short Notice Respite], [Hospice Stats 08-09
14-5-09].[Hospice Initiated Stays], [Hospice Stats 08-09 14-5-09].[Family
Initiated Stays], [Hospice Stats 08-09 14-5-09].[Emergency Type], [Hospice
Stats 08-09 14-5-09].[Emergency Stays], [Hospice Stats 08-09 14-5-09].[No of
Cancelled Days], [Hospice Stats 08-09 14-5-09].[Party Cancelling], [Hospice
Stats 08-09 14-5-09].[No of Funded Nights], [Hospice Stats 08-09
14-5-09].[Total Days In Hospice], [Vol Master List 15-5-09].[VOL SAAP No],
[Vol Master List 15-5-09].vfirstname, [Vol Master List 15-5-09].vsurname,
[Vol Master List 15-5-09].vstreet, [Vol Master List 15-5-09].vCITY, [Vol
Master List 15-5-09].vSTATE, [Vol Master List 15-5-09].[vP CODE], [Vol Master
List 15-5-09].vhphone, [Vol Master List 15-5-09].vwphone, [Vol Master List
15-5-09].vmobphone, [Vol Master List 15-5-09].vemail, [Vol Master List
15-5-09].[Lind Pendg], [Vol Master List 15-5-09].ANP, [Vol Master List
15-5-09].Resting, [Vol Master List 15-5-09].[Review Date (only if N)], [Vol
Master List 15-5-09].[Home Vol], [Vol Master List 15-5-09].[Family SAAP No],
[Vol Master List 15-5-09].[Family Surname], [Vol Master List 15-5-09].FSW,
[Vol Master List 15-5-09].RCH, [Vol Master List 15-5-09].Hospice, [Vol Master
List 15-5-09].[Rostered Day], [Vol Master List 15-5-09].[Activities Only],
[Vol Master List 15-5-09].SP, [Vol Master List 15-5-09].BSP, [Vol Master List
15-5-09].AP, [Vol Master List 15-5-09].MMC, [Vol Master List 15-5-09].[Rem
Day], [Vol Master List 15-5-09].[B Parent Weekend], [Vol Master List
15-5-09].[Creative M], [Vol Master List 15-5-09].[Coffee Mornings], [Vol
Master List 15-5-09].[Hosp Sch Hol], [Vol Master List 15-5-09].[Xmas Party],
[Vol Master List 15-5-09].[Reg Family Day], [Vol Master List 15-5-09].Admin,
[Vol Master List 15-5-09].Friends, [Vol Master List 15-5-09].[VTP Group No],
[Vol Master List 15-5-09].[VTP end date], [Vol Master List 15-5-09].[Police
Check], [Vol Master List 15-5-09].[WWC Expiry], [Vol Master List
15-5-09].[All Vols], [Vol Master List 15-5-09].[Continuing Links], [Vol
Master List 15-5-09].[No Mail]
FROM ([Master Family List 2-6-09] LEFT JOIN [Hospice Stats 08-09 14-5-09] ON
[Master Family List 2-6-09].[ID No] = [Hospice Stats 08-09 14-5-09].[SAAP
No]) LEFT JOIN [Vol Master List 15-5-09] ON [Master Family List 2-6-09].[ID
No] = [Vol Master List 15-5-09].[Family SAAP No]
ORDER BY [Master Family List 2-6-09].[Child's Surname];

I have also written numerous action queries to produce reports/mailing
labels for each of the 3 groups. They are working fine.

My problem is to alter the 1st query to link the 3 tables but not mutually
exclusively - likewise not all encompassing. I'm also not sure using the
some of the same field on the 3 different tabs - will this be causing the
tabs to show ALL 855 records instead of the smaller number?

Thanks Cathydal
Forget about the tab control on the form for the moment and concentrate on
the 'logical model' i.e. what is being modelled by the database in terms of
Click to expand...
[quoted text clipped - 39 lines]
tabs. I'm not sure if I have gone about this all wrong, but I'm too far in
to rebuild. Please help
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

I see where you are going with this but first I must explain some things.
The multiples are because in a child's family there can be more than one sick
sibling (up to 4) and more than one sibling (up to 5) and 2 parents so they
each have a gender and the siblings each have a date of birth (some having a
date of death).
Click to expand...

"Fields are expensive. Records are cheap".

if you have five sibling fields, what will you do when a family comes in with
eight? Or if you have eight... how about nine?

You're using a relational database, not a spreadsheet!!! Use it as it is
designed. Reread Ken's overview. Your table structure is incorrect, and his
advice will lead you in the right direction.
 
C

Cathydal

Thanks John, The number set for siblings is arbitrary and will not expand
(as the relevant ones are b/ween a certain age only). I take your point
though about the way it is set up. As I said to Ken, I'm not sure now how
really to split my tables in any meaninful way as there is so much data in
the master file. Should I be trusting access to split the tables?

Cathydal.
 
J

John W. Vinson

Thanks John, The number set for siblings is arbitrary and will not expand
(as the relevant ones are b/ween a certain age only). I take your point
though about the way it is set up. As I said to Ken, I'm not sure now how
really to split my tables in any meaninful way as there is so much data in
the master file. Should I be trusting access to split the tables?
Click to expand...

As a suggestion and as a guide, yes. As a reliable and absolute truth,
absolutely NOT. Relational table design is a skill and an art, and yours is a
fairly complex data structure. A USB (Using Someone's Brain) interface is
absolutely essential!

Each table should represent a particular entity type. A person *is an entity*,
and each person in your application should be represented by a single record
in a table of People. Relationships between people should be modeled by
relationships between records in a table - for example, you could have fields
FatherID and MotherID in the People table as foreign keys in a self-join
relationship to the parents' records in the same table. Other designs are
possible and may be appropriate; if you're concerned only with genetic
relationships then each person has only one mother and only one father, but if
you're concerned with social/custodial arrangements you might have many to
many relationships, I don't know.

If you haven't already, take a look at some of the resources here
(particularly Crystal's Database Design 101 tutorial):

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

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

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

John... Visio MVP

Steve said:
Hi Cathydal,

I responded to your thread twice before and have been following your
frustation. I provide help with Access applications for a reasonable fee.
I would like to offer to work with you to come to a design of your tables.
My fee would be very modest. Contact me if you would like to work
together.

Steve
Click to expand...


stevie, you are like one of those Sunday school teachers who admonishes
their flock for not being considerate to their nieghbours and then while
they have they have their heads down in penatence, is rifling through the
cloakroom looking for change.


These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
 
G

Gina Whipp

Cathydal,

Do not pay for help for designing your tables when you can get that service
for FREE right here. If you just answer John's inquiry into the tables you
have, he or some other volunteer will help you for FREE.

--
Gina Whipp

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

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

Cathydal

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.

I'm a little scared to move forward now. Cathydal
 
G

Gina Whipp

Cathydal,

Below is a beginning, a jumping off point to get you thinking. You must
think of table set-up like building a house... great foundation, great
house, bad foundation bad house... and you can't put furniture in the house
till the foundation is up, the walls are up and painted, the place gets
inspected. Afterall, you don't want your house to fall down while you're
living in it.

tblGuardian
gGuardianID (PK)
gGuardianTypeID (FK-relate to tblGuardianType)
gFirstName
gMiddleInitial
gLastName
gSex (M or F)-can't always tell by name
etc...

tblGuardianType
gtGuardianTypeID (PK) (M=Mother, F=Father, SM=Step Mother, SF=Step Father,
GM=Grandmother, GF=Grand Father, S=Sister, B=Brother, etc...)
gtDescription

tblChildren
cChildrenID (PK)
cGuardianID (FK-relate to tblGuardian)
cChildrenTypeID (FK-relate to tblChildrenType)
cFirstName
cMiddleInitial
cLastName
cSex
etc...

tblChildrenType
ctChildrenTypeID (PK) (S=Sister, B=Brother, SS=Step Sister, SB=Step
Brother, GD=Granddaughter, GS=Grandson, etc...)
ctDescription

tblMedicalInformation
miMedicalInformationID (PK)
miGuardianID (FK-relate to tblGuardian)
miChildrenID (FK-relate to tblChildren)
etc...

--
Gina Whipp

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

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

John W. Vinson

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.
Click to expand...

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.
 
C

Cathydal

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
 
J

John W. Vinson

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?
Click to expand...

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

Cathydal

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
 
J

John W. Vinson

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

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.
 
C

Cathydal

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
 
G

Gina Whipp

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
 
C

Cathydal

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
 

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

Similar Threads

How to set tabs in work document 0
How can the font and size of (all) citation numbers be altered? 1
linking tables and relationships 3
Using Styles In An Outline 0
Remote stopped working in PowerPoint Presenter View 0
one-to-one connections 3
Linked SQL Tables don't report Cascading Updates and Deletes 2
Table Relationships: An Understanding is necessary... 8

Top