Help, Form create 2 tables?

[morfusaf]

First thanks,

Summarization ...I want to create a form, that in-turn populates a table and creates another table (using a template, and being named with one or more of the textbox information).


Ok, So I am planning on creating a database that tracks personnel and events. I need to be able to pull reports from specific person/people. So my plan is to have 1 database, 1 table(called INFO) that keeps personal info(like Last hame, First name, phone number, work center)... Rows being per person, and columns being the info added. Then I figured I would have a table for each person(labeled lastname firstname) created to track things such as, event/job(by date), then have how many hours, how far traveled how much money spent ect for each event.
So the rows would be the event/job and the columns would be hours, miles, blah blah.

I am doing it this way so I can pull reports, like between the last 30 days, how many hours did SO&So work and how many miles did he travel. but still have them listed out by jobs and not added all together.

Where I need help is, getting a form to create these.

I will create a table template for the (lastname Firstname) tables, but I want to create a form where you input the information for the INFO table, then when you submit it, it adds the person in that table... Also, when you submit that I want to be able to create the (lastname Firstname) table using the template I created, and naming it from the lastname and first name boxes.

So, form has last name, first name, phone... blah blah...
Click submit, this adds the person in the INFO table, and creates a table using the template and naming it Lastname_Firstname....

Can anyone help me code this? i know I need to code the action on the submit button, but I have no idea how to make this happen. Any help would be greatly appreciated.

 

[morfusaf]

Humm... I have never done one like this, Would you still be able to pull reports by individuals? Meaning, what did SO&SO do from date to date, and all the events with times/hours/miles come up?


And how would the people at events table work? Rows= events &columns =people....

Also, would you be able to update if 1 person say, didn't spend the whole time there, so 1 person only did 4 hours instead of 6? ... seems likes that wouldn't work.


Could you or somone help me out with the 3rd table you explained? I am not quite understanding how that works. Thanks a bunch. Also what would be the best way to link that table to the people and events? I guess I need to search for info on junction tables.

 

[morfusaf]

ALSO,

I was wanting to be able to put what each person does at said event,

So Event 1, "john" was the locksmith, bill was the fire fighter(not real jobs hehe). ... Would this be possible with this junction table process?

 

[morfusaf]

Humm,

Do you have a simple example you can upload or something, I just ain't getting it...

.... What I am understanding is,

Column 1= People At Events, Column 2=Person ID, Column 3=Event ID...
Row 1 = First event
Row 2 = Second Event
Row 3 = third event

Each row, being populated with corrisponding information in the columns.

--------------------------------------------------------------
Is that correct?
-----------------

Also, how do you get more than 1 personID for the same event?

Im going to do some reasearch on Junction Tables to understand how these IDs will work... Anyone have any quick references to help?

 

[jdraw]

Further to Pat's example, I am attaching links to some free video tutorials. These tutorials deal with database concepts.

Get your tables and relationships set up before worrying about the details of forms.

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

 

[morfusaf]

Thanks Both thats very helpfull, hopefully I can figure this one out.

 

[morfusaf]

Access 2007 Used.

Ok, So I started from scratch and deleted all my forms and everything.

I currently have the following tables

tblEvents (EventID Primary key, Autonumber(this way it makes it for me))
tblPSNL (PSNLID Primary Key, Autonumber(Same reason))
tblPSNLAtEvent (PeopleAtEvent (Autonumber), EventID (number), and PSNLID (Number) all Primary keys).

I did all 3 as primary field because of this website
http://support.microsoft.com/kb/304466

"In the junction table, set the primary key to include the primary key fields from the other two tables. For example, in an TitleAuthors junction table, the primary key would be made up of the OrderID and ProductID fields. "

Also,

I have the following relationships.

tblEvents (EventsID to EventsID on tblPSNLAtEvents)
tblPSNL (PSNLID to PSNLID on tblPSNLAtEvents)

Is this correct for what I have explained I needed?

Also, each relationship defaults as a one-many relationship(not sure but seems like I might want a many to many? not sure... confusing!


IF SO...

I need to move on to forms, How do I work the forms/get it to put info properly. Keep in mind I have never used a Junction table.

So, I want to create Event (with all corrisponding info), then have a section(mabye a subform or something using a multibox) to select which people attended this event...

-----------------------------
I want 1 form for inputing people(easy enough) got that...

I want another form for inputing events and all information like date, time, blah blah.... in the same form, i want to be able to select who attended this.... Then I want to be able to submit/update it and it properly put events/people on the tblPSNLAtEvent

Thanks for any help again guys/gals!

 

[morfusaf]

Hum..

Every where I read told me to make them a "Compound or Composite Key".

Ahhh why is this so difficult.

OK, so if they are not compound keys, will it still work correctly? I don't really understand what the whole key thing does for them.

 

[morfusaf]

OK, So I have the 3 tables, I have the ID(eventID, personID...) in each as the only Primary keys for these.

I have relation ships like I posted above, eventID-eventID...

Now I am creating forms.

I created my formAddPeople first, as I figured I need people inorder to populate who attends the event.

I created a formAddPeople, formDeletepeople, and formUpdatePeople... Easy as pie...

Now, I am moving on to the Create event form.

In this form I need to be able to input all the information about the event (date, location, POC, vehicle used... blah blah) and be able to select who all attended this event.

So, I know how to handle the infomration for the event, but now how do I add the people? I am pretty sure I need to do mainform/subform, I currently am thinking I need to do a formCreatEvent as the Mainform (bound to the tblEvents) then do a subform... But which tbl should I use? and how do I link it all together in the Junction table?