Table Troubles

[BartK]

Hi all,
I'm having a little trouble setting up a new db in access 2010. I'm in the VERY EARLY BEGINNINGS of it and I think I know what the flow of it needs to be, however I've never dealt with multiple tables before so here is what I have.

I have 4 tables and they are as follows:
Employee, Radio, Spotter, Phone Employee table is my main table.

I have been able to save information in a form into a single table, however when I want to save information from one text box on my form to a different table I am lost.

I'm sure that this is something very simple, but like I said I don't really know how to proceed. Any help in this would be greatly appreciated.

 

[JHB]

Have you "drawn" the relationships between the tables in the "Relationships Window" and set the "Enforce Referential Integrity"?
Base your form's "Record Source" on a query, which included the necessary fields from the different tables.
Else post your database with some sample data, (zip it).

 

[BartK]

In "drawing" my relationships I have went from ID on the employee table to ID in all the other tables. Like I said very new at multiple tables. I will attach the db and hopefully you can make some sense out of it.

Ultimately what I am trying to get to is from my main form that everytime that I input that something that it is stored in it's respective table and later on if I want to do a search of Employee John Doe then it brings up everytime I have input John Doe and which phone he had at that time.

I might be shooting for the stars here but I don't know just a thought.

 

[JHB]

Sorry, I'm a little lost here, are you making an input form, or a form from where you can search?
You haven't set any "Record Source" for the form you have in the database, (and all the controls in the form is therefore unbound), is it here you have the problem?

 

[BartK]

The form that I have there is just for input only, I have not set up anything beyond that yet, I was having some trouble getting the information from the form to being stored in the tables. I would just like to know how to get the tables setup correctly. I will use a report in order to filter my searches later on.

Sorry for being a little behind on this, really new to multiple tables and the way they interact with one another. Thank you once again.

 

[JHB]

I've made a simple form "FormQuery" for you for inputting data, I have also change your tables. You can't link an Autonumber field to an Autonumber field.

 

[BartK]

Thank you for that, how did you get the information from the form to be saved in the corresponding table? I was having problems with that, on the drop down arrow under the control source it did not give me that option. Is that something that is handled thru the query or did you have to manually type that in.

 

[JHB]

Try to create a new record and fill in some information, then you'll see it is done "automatic" by the query.
You can also hit the 3 dots "..." to the very right in the Control Source and build a query there.

 

[BartK]

So in order for me to save information from the form I have to build a query? This might sound a bit remedial, but in a single table I was able to hit the drop down arrown in the control source and select where I wanted my information to go. Since I have 4 tables now do I have to go about it a different way?

Thanks for all the help.

 

[David R]

If the form is built on a single table, yes you can use a single table as the recordsource. Now that it is drawing from several tables you will need a query (or at the least some SQL, but Access will run faster if you save it as a stored query).

As JHB said, use the [...] button to build your new SQL, then Save As... and name it to a query.

 

[BartK]

Ok, I didn't know that. Thank you for the clarification. One other question I have. On my relationships between the tables do I want one-to-one or one-to-many? I know it probably matters what I want to do with it huh.

Ex.

John Doe (employee table) employee number 12 (employee table)was issued Radio 23 (radio table) and phone 45 (phone table) and gas detector 35 (spotter table).

I save the information and it goes into it's individualized tables and if/when he returns all his equipment I could select a button to delete those records in those tables just from when he checked the equipment out.

Make sense? Probably about as clear as mudd.

 

[David R]

Ok, I didn't know that. Thank you for the clarification. One other question I have. On my relationships between the tables do I want one-to-one or one-to-many? I know it probably matters what I want to do with it huh.

Now you're catching on.

John Doe (employee table) employee number 12 (employee table)was issued Radio 23 (radio table) and phone 45 (phone table) and gas detector 35 (spotter table).

Does every employee always have (at most) ONE phone, one radio, and one gas detector? If so yes, one-to-one will work. But what if they lose one/leave one at home, check out another for the day, then return it tomorrow and bring back the other?

I would definitely use Form/Subforms here. Your main form is the employee's info, the subforms each link that form to a particular Radio/Phone/etc table (you could combine it all into one big Equipment table with a EqType = "Phone", "Radio", etc, but that might be overkill). Then you're back to each form having basically one table behind it, and everything stays editable. And you can still have a button that says 'deactivate all devices for this employee, I have thrown their butt out the window'. Delete is kinda evil, you never know when you may want to run a report on old employee info...

For bonus points, this layout allows you to track what phones an employee used to have, or lost, or ran over with the truck 17 times, or whatever... a one-to-one works fine for simple operations, but you can capture more data this way.

 

[BartK]

David,
That is exactly what I want to do, being able to keep records of what equipment a person was issued and if that equipment didn't come back then I could "alert" whenever that equipment doesn't come back/ran over etc. In essence to keep a "running tally" of who is messing up. Doing everything on paper now then over to excel to be e-mailed. Wanting to get it down in access and then once a week have it automatically e-mail reports out to people who make more money than I do. Thank you for your help on this.