Question Need help to creat my first relational database

[LearnAcces]

Hi all,
I need to create my first Access database, I hope you can help me with my concerns and thanks in advance to all who are willing to help me.
I have attached an excel file containing the type of data which will be entered in the Access database (sheet Records) and the 2nd tab is a sample of report wich will be produce from data.
In the Access file I attached, I started creating my access database.
My questions are:
- Someone told me that it is better to create relational database, I know how to create tables in access but I don't know how to link them then, how to create relations.
- Is there any possibility to import my data from Excel to Access base on my table design on access? (I don't wanna keep my excel format)
That is all for now hope to get help from you guys.

Thank you.

 

[Alansidman]

No file attached.

 

[LearnAcces]

Thanks Alan!
Now they are attached.

 

[Alansidman]

First, you will not be able to import your Excel table as it currently stands as it is not in a table format.
I will not be able to open your Access file until later today as the machine I am working on currently has only AC2003 loaded.

I would urge you to read the following link before proceeding.
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

 

[Chrisopia]

you need to remove all repeated headings in excel... only have one at the top. Also avoid merged cells, it makes it less messy when importing. The grey cells needn't be greyed out for importing purposes.

Things like DATES shouldn't be 3 separate fields.
tblPerson
[ID] [First Name] [Last Name] [DOB] [age at intake] [Gender] [Date Intake] [Date file Closed] [File Status] [OTIS] [Referral Type ID] [Referral Sub-type ID] [Prog ID]

tblProg
[ID] [Prog] [Type ID] [Start Date] [End Date] [Program Status] [Action] [Hours] [Notes]
[Charges ID]

tblCharges
[ID] [Pending Intake] [Sentences] [Priors] [New Changes]

if things have a lot of items that need referencing and updating then give them their own table, for instance [Type ID] could be linked to a table with Prog types, but you could very well make it a look up list if you dont need to add and reference it a lot.

tblRecord
[ID] [ProgID] [PersonID]

[tblPerson] ---< [tblRecord] >--- [tblProg]

[tblProg] ----< [tblCharges]

without playing with it in real life i cant be exact with my advice. Plus the purpose of the database isn't obvious which does help shape the structure. But I hope this helps somewhat.

 

[LearnAcces]

@ Alan, thank you for the link, It helping me a lot.

@ Chrisopia
Thank you so much for this work.
I just need to clarify some part:
When you put :

tblPerson
............ [Prog ID]
tblProg
[ID] [Prog] ........

Are [Prog ID] in tblPerson and [Prog] in tblProg exactly same field? I f not what values would contain [Prog ID]?
Same question for [Charges ID] in tblProg , what is its meaning?
In tblRecord
[ProgID] = ? [PersonID] =?

Also, I think I will have to add 5 fields for Program because the same client can do many differents programs (As we can see into the Excel file column P-Z).
So:
tblProg
[ID]
[Prog1] [Type ID 1] [Start Date 1] [End Date 1] [Program 1 Status] [Action 1] [Hours 1]
[Prog 2] [Type ID 2] [Start Date 2] [End Date 2 ] [Program 2 Status ] [Action 2] [Hours 2]
...
...
[Prog 5] [Type ID 5] [Start Date 5] [End Date 5] [Program Status 5] [Action 5] [Hours 5]
[Notes]
[Charges ID]

For now, my big concern is how to create the relationship between my tables?

Any additional help would be very appreciated.

Thanks

 

[jdraw]

I suggest you read this info on relational design and normalization before you go any further.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

And then read this article on how to build tables and relationships.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

If you are going to use relational database, better to start with some planning and a design.

 

[LearnAcces]

Thank you all I am gonna read these documents you have sent to me.
I'll be back if I have questions.

 

[Chrisopia]

@ Alan, thank you for the link, It helping me a lot.

@ Chrisopia
Thank you so much for this work.
I just need to clarify some part:
When you put :

tblPerson
............ [Prog ID]
tblProg
[ID] [Prog] ........

Are [Prog ID] in tblPerson and [Prog] in tblProg exactly same field? I f not what values would contain [Prog ID]?
Same question for [Charges ID] in tblProg , what is its meaning?
In tblRecord
[ProgID] = ? [PersonID] =?

Also, I think I will have to add 5 fields for Program because the same client can do many differents programs (As we can see into the Excel file column P-Z).
So:
tblProg
[ID]
[Prog1] [Type ID 1] [Start Date 1] [End Date 1] [Program 1 Status] [Action 1] [Hours 1]
[Prog 2] [Type ID 2] [Start Date 2] [End Date 2 ] [Program 2 Status ] [Action 2] [Hours 2]
...
...
[Prog 5] [Type ID 5] [Start Date 5] [End Date 5] [Program Status 5] [Action 5] [Hours 5]
[Notes]
[Charges ID]

For now, my big concern is how to create the relationship between my tables?

Any additional help would be very appreciated.

Thanks

If the client can manage many programs, then that calls for a one - many relationship. Clearly Programs should be linked to the client through Client ID...
Instead of [progID1][ProgID2][ProgID3] there should only be 1 progID.

I still can't see clearly what's happening... but what I learned in school about relationships was to play around until you achieve a one to many relationship.

For example, 1 customer can have many products, many products can belong to many customers, so you add a field in between to unify them in a normal relationship:
Customer -< Invoice >- products

the table: invoice, will contain both foreign keys for Customer and products, e.g. customerID and ProductID. Invoice will have it's own unique ID - InvoiceID. This is what i tried to explain in my example.