Design Help

[Brightspark98]

Novice User so please be kind!



Hi,

I'm looking for assistance on the best way to structure a database,

The table in question is a year planner divided into weekly sections.

Field1 would be the ID
Field2 Would be Member Name or MemberID stored on another table. (100+ Members)

Question is do I need another 52 Fields numbered or dated in weekly increments to specify an activity for each of the Members.

The choices for each record would be from LookUp on the activities table.

ie.

ID	Name	Wk1	Wk2	Wk3	Wk4	Wk5	Wk6	Wk7	Etc.....
1	Jack	Drive	Cycle	Walk	Drive	Fly	Fly	Cycle
2	Gary	Walk	Fly	Walk	Cycle	Drive	Drive	Walk
3	Roy	Fly	Cycle	Walk	Drive	Cycle	Walk	Fly
4	Jane	Cycle	Walk	Cycle	Drive	Fly	Fly	Walk

Am I looking at this the right way or am I stuck in 'Excel Mode?'

I hope this makes sense.

Thanks

[JoeM]

Yes, that is Excel mode, and not how you want to structure it in Access. It could cause you nightmares.
Think of if you wanted to look across all weeks to find any record equal to "Fly". You would need to write criteria for 52 different fields! (Yikes!!!)

You would want four fields in this scenario:
ID
Name
Week Number
Value

So, you would have 52 different records for each person (instead of one record with 52 week fields).
This makes it much easier to work with (and follows the rules/best practices of Database Normalization).

[ranman256]

your table will look like this for each person:
ID Name Wk Event
1 Jack Wk1 Drive
1 Jack Wk2 Cycle
1 Jack Wk3 walk
etc

the chart you gave would be a Crosstab query of the data I gave you.

[Brightspark98]

Thanks for the quick response.

So If I had 100 Members @ 52 weeks I would need 520 entries on the table?
I get the error I'm creating duplicate entries when I try to enter the same name in the next record.

Sorry when I said novice I meant TOTAL NOVICE!!

[JoeM]

You would get that error message if you have a Primary Key set-up and are using duplicate values in it.
Your ID field in this table should probably be tied to each record for this table, and not each individual, so it would look like:

ID Name Wk Event
1 Jack Wk1 Drive
2 Jack Wk2 Cycle
3 Jack Wk3 walk
...

Typically, you would have a separate table in a database like this that holds the clients/people, one record per person/client. That would have its own ID also.

[Brightspark98]

You would get that error message if you have a Primary Key set-up and are using duplicate values in it.
Your ID field in this table should probably be tied to each record for this table, and not each individual, so it would look like:

ID Name Wk Event
1 Jack Wk1 Drive
2 Jack Wk2 Cycle
3 Jack Wk3 walk
...

Typically, you would have a separate table in a database like this that holds the clients/people, one record per person/client. That would have its own ID also.

By that do you mean if I had 100 people I would need 100 tables 1 table per person?

[JoeM]

By that do you mean if I had 100 people I would need 100 tables 1 table per person?

Absolutely not! That would be insane!

You would have two tables:
- One for people (one record person)
- One for what you are trying to do here, structured as we described

You may actually have other tables, but these are the only two relevant to your question here.

[Brightspark98]

That's what I thought too!!.

I already have the Members Details table so can reference the MemberID from there.

The the new table would consist of ...

ActivityID---MemberID---WeekNo---Activity.

Then would I need 52 entries for each member as described above. Wk1 through Week52. That would mean 520 records for 100 members.

I do appreciate your patience & help.

Many Thanks

[JoeM]

Yep, you got it now!

[Brightspark98]

Thank you,

Following on.......

Is there a quick way to populate the records As the Activity would be a lookup/ComboBox and the first three would be incremental by 1 up to week 52.

Do I need to make a new post for this?


Just a thought.... If I create the table in Excel and import to access that would do it for Fields 1-3 just the lookup I would be struggling with.

Just realised the Lookup is created in Design and relates to the whole field. If I import the excel file would the lookup still apply or would it be overwritten.

I am not at my laptop so don't have access to try. I will continue later. Many thanks.