New to Access, Need some help in understanding how to structure my database

[MakinBacon91]

Hello all,

I am having some trouble getting started with Access.
I have a lot of experience with Excel, and VBA, but it seems like an Access database would be a lot more powerful for what I am trying to achieve.

I am just trying to evaluate some of the Access Templates provided by Microsoft for now. (Specifically the Work order management database under Industry templates)

Here is an image of what I am confused about: Pasteboard — Uploaded Image

It shows that CustomerID is linked from Customer Table to Workorders Table.
In the Customer Table, the CustomerIDs are 1, 2, 3, ...
In the Workorders Table it shows CustomerID as "Wide World Importers", "Coho....", "Fourth...", ...

Could someone explain how this works?

Thanks a bunch in advance!

 

[Micron]

They should not be. The row values should be 1,1,2,2,2,3,3,4,5 - otherwise, what's the point of having a customer id of 1? If there is a similar Employee table, the IDs should be showing instead of the names. Not only does your example seem to be not properly normalized, it appears to be based on lookup tables. You should avoid these like the plague. Look here The Access Web - The Evils of Lookup Fields in Tables
Allen Browne's access website is also a valuable source of learning information Microsoft Access help, tutorials, examples

 

[MakinBacon91]

Haha, well that makes me feel a little better. I was thinking the exact same thing about customer ID (why have it numbered at all?). The Microsoft templates are all set up like this.

Also, thank you for the two links, I will have to spend some time perusing them.

Just to verify, "Lookup Fields", is referring to the Data Type? I.E. creating the data type with a lookup wizard based on another table?

 

[Micron]

If you can click in a table field and a drop down appears so you can select values from somewhere else, that's a lookup field. The image you linked shows the plus signs in the table fields, which is what made me think that's what you were dealing with.

 

[MakinBacon91]

I guess my question for you would be, how can you have a lookup table, without having a lookup table?

For my application this is very important because I need to limit the users input to only the parts that are in a given set.

haha, very broad, but maybe you could point me in the right direction.

 

[Micron]

I need to limit the users input to only the parts that are in a given set.

You control what users can see and interact with by using forms and reports. If need be, you "lock down" as much of the database as needed to keep users out of places they should not be.

 

[MakinBacon91]

You control what users can see and interact with by using forms and reports. If need be, you "lock down" as much of the database as needed to keep users out of places they should not be.

Well it is not soo much that I want to lock users out, but I want their data entry to be directed.
We are a repair shop for gas turbines, and have several different turbines that we work on.

When a user selects a turbine type, I want to limit other fields to only give them an choice between options that are available for that specific turbine type

I.E. Turbine A has 3 stages, and Turbine B has 4 stages. If the user selects Turbine A I do not want them to be able to select a 4th stage component.

In excel, I controlled this by having a combo box that would lookup what components the selected turbine has, and then generate a list for them to select a stage.

Thanks,
Bacon

 

[Micron]

You would do the same in Access. There are lots of posts for filtering cb's