Cross Reference Tables (1 Viewer)

Hi all,

I have a very simple product database that I am designing, and am at the planning stage. This is probably a very basic question but I have never built a database from scratch before.

In one Table ("Main Table"), I have the fields: "Item Number", "Description", "Shipment Terms" and "Shipment Costs".

All the fields have manual inputs except for "Shipment Terms" and "Shipment Costs". "Shipment Terms" is restricted to a list of 10 set terms.

I want another Table ("Shipment Table") to have the list of those 10 terms, plus the "Shipment Costs" associated with them.

In the "Main Table", I now want the "Shipment Costs" to automatically pull back the relevant "Shipment Costs" from the "Shipment Table".

Is that possible or am I misunderstanding the concept of database structures?

Many Thanks,
Nick

Given that there is a one to one relationship between shipment terms and shipment cost in your table you need only store the ShipmentTermID and the other two fields can be collected, for display purposes , using a query. Become familiar with the concept of data Normalisation.

Now would also be a great time to consider a naming protocol for your DB objects, something along the lines of TBL_TableName, FRM_FormName, QRY_QueryName, RPT_ReportName Etc. also limit your self to alpha and numeric characters and the underscore.

Hi John,

Thanks very much for that - I will get into the habit of using the alpha and numeric characters and use the underscore.

I also get where you're coming from regarding the storage in 2 tables and using a Query to view the 'combined' fields.

Having looked at it more, I will probably need to generate a very user friendly Form as the end users have no access skills.

I therefore need them to be able to go to this Form to create a new "Item Number", fill in the "Description" and select the relevant "Shipment Terms" from the list. I also want the form to show what the "Shipment Costs" are for the "Shipment Terms" selection made (therefore cross referencing the "Shipment Terms" table), but I'm tearing my hair out to try and get it to work.

(I'm obvioulsy simplyfying what I'm doing - there will be plenty more fields and other tables involved, but if I can get to understand this the rest should be OK)

Do you have any suggestions?

Many Thanks,
Nick

I agree with John that Normalization is a key.
Also there are some data models that may be of interest at
http://www.databaseanswers.org/data_models/index.htm

Good Luck.

Hi guys,

I've understood the concept of data normalization and have set up my relational database structure.

I now need to be able to put together a user-friendly form that can amend and update the information across all related tables in my database.

Do you have any suggested sites/resources for Form development as I now have several detailed queries I need to play with regarding developing the form?

Many Thanks,
Nick

Hi again,

I've attached my file this time. I will be fully normalizing the structure but for the moment I am still dealing with the concepts invloved.

If you take a look at my form I have the 3 fields there. I want to add the "Product Group" Field from the "Proteus Group Details" table to this Form as well.

However, I want the "Product Group" to be automatically "looked up" - possibly using an OnClick event when selecting the "Proteus Group Number" on the Form. This field should not be updateable in any other way.

Does that help explain what I'm trying to do?

Regards,
Nick

I'm working with Acc2003 and can't open a accdb.
Here is a site that has some good info and tutorials on various Access topics.
http://www.fontstuff.com/access/index.htm

You can look at Dynamic Reports for samples of Forms and vba to do various things.