Need help with a Report design

[Sothpaw]

i'm making a family, friends, clients database in Access and all entries are stored in a table. my ultimate problem is with birthdays. i don't want a seperate entry for each person, i want all family members of a given family to be under the same entry but with seperate first names like "Name1","Name2",etc. on their own column and each name has an attached birth date and check box if i send them birthday cards.
i want a report to return address labels for each birthday card recipient but so far all i can get is either "Name1" or "Name2" depending on which way i code it. i think the answer lies in some if or statement but i'm not sure how to do it. any suggestions?
thanks,

 

[Alisa]

i'm making a family, friends, clients database in Access and all entries are stored in a table. my ultimate problem is with birthdays. i don't want a seperate entry for each person, i want all family members of a given family to be under the same entry but with seperate first names like "Name1","Name2",etc. on their own column and each name has an attached birth date and check box if i send them birthday cards.
i want a report to return address labels for each birthday card recipient but so far all i can get is either "Name1" or "Name2" depending on which way i code it. i think the answer lies in some if or statement but i'm not sure how to do it. any suggestions?
thanks,

That sounds like a pretty poor design - the proper design would be one table for "families" and a separate table for "family members" with a FK to the families table . . .

 

[Sothpaw]

thanks for the reply,
yeah i don't doubt it is a poor design. but i'm learning and constantly doing it differently and better. as i was browsing tutorials today i started to wonder if the names being somewhere else would be the answer.
i'm going to start finding out what FK means now and give your idea a shot.
thanks.

 

[Sothpaw]

i haven't found what and FK is yet.
does a Subform seem like it will work?

 

[Alisa]

FK = foreign key
Yes, when you have a one to many relationship, a main form with a subform is a common way to go.

 

[Scooterbug]

i haven't found what and FK is yet.
does a Subform seem like it will work?

FK = Foreign Key. A FK is a number that is stored in a record that references a record from another table. This allows you to link a record from one table to another table. For example:

You have a Family member named John Smith. You create a table which stores all relavent data for the Smith family. The ID number for that record would be say 1. In John Smith's record, you would have a field called FamilyID. You would store the number 1 in that field.

The reason for this is that there can be many members of the Smith family. By recording the details of the Smith family in one location, you are not entering the same data over and over again.

 

[Sothpaw]

i'm getting the FK and Subform idea... however.
I don't want to list by individual. i want to list by family/location with a couple extra columns for 1,2,or 3+ family members with their birthdate and a check box if they get a card.
i'm thinking the answer lies in my report filter.
can a report filter filter more for more than one yes box? or would that make it need both or all yes boxes to give a listing? i've played around with IF but that's giving me syntax errors.
any ideas?
i do keep trying to consider a Subform but it's not a one to many relationship. it's a one to one or many to one.... kinda backwards i know but i don't want a seperate form for each of my clients children that get birthday greetings etc.

 

[Alisa]

i'm getting the FK and Subform idea... however.
I don't want to list by individual. i want to list by family/location with a couple extra columns for 1,2,or 3+ family members with their birthdate and a check box if they get a card.
i'm thinking the answer lies in my report filter.
can a report filter filter more for more than one yes box? or would that make it need both or all yes boxes to give a listing? i've played around with IF but that's giving me syntax errors.
any ideas?
i do keep trying to consider a Subform but it's not a one to many relationship. it's a one to one or many to one.... kinda backwards i know but i don't want a seperate form for each of my clients children that get birthday greetings etc.

I know that sounds like a good idea, but it's not. You really need to spend some time reading about database normalization - there are plenty of articles for beginners all over the internet. Once you've got the correct structure worked out, the form design will fall into place. Good luck . . .

 

[Sothpaw]

Database Normalization.... will do. thanks.