Relationships, Lookups, Subforms

[Bastian]

I am a newbie. I need help very badly.

I have two tables: tblinventory and tblpartslist. I want to create a one-to-many relationship between them, and Access isn't allowing it. I have no idea why. The data types are the same, and there are 5 fields in each table that should correspond to each other.

I need to create a form from tblinventory, where I will need to enter parts records from tblpartslist. I would like to create a subform in tblinventory, where I will be able to look up records in tblpartslist by the product numbers, and enter them into a datasheet. Both tables have this field. I would like the information from tblpartslist to automatically enter into the corresponding fields in tblinventory, so that I don't have to enter the info in over and over.

I can't create an AutoLookup query, because I can't make a 1:m relationship.

I hope I'm being clear enough. Someone, please help me. I'm having a very hard time getting anyone to answer any of my questions, and I can only improve by continuing to ask. I have no VBA experience, but I'm really interested in learning more about it. I have to start somewhere... I need help from the experts!

 

[KenReay]

Hi

Give us some information

Where are you trying to specify the relationship?, in the relationship window, or in the query buidler window?

Do you get any error messages, if yes what do they say

Give us briefly you table structure, column names, types, lengths (if text) will do for starters

Regards Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[Bastian]

I’m trying to specify it in the relationship window. I don’t get any error messages, it just automatically is a 1:1, and I don't have the option of changing it. I thought that maybe it was because the data types were different in the five corresponding fields, but they're all the same. Is there any other criteria I need for a 1:m?

tblinventory has these columns:

invoicenumber – long integer (key)
salesordernumber – long integer
jobname – text, 20
dateshipped – date
productnumber – long integer
description – text, 50
unitmeasurement – text, 4
price – currency
weight – double
qtyordered – long integer
qtyshipped – long integer
netamount – currency
invoicetotal – currency


tblpartslist has these columns:

productnumber – long integer (key)
description – text, 50
unitmeasurement – text, 4
price – currency
weight – double

 

[KenReay]

Hi

It is because you are joining on so many columns

Why aren't you simply joining on ProductNumber, since this is the PK of tblPartsList it will give you a 1:m with tblInventory.

As an aside why do you have description, UnitMeasure repeated in both tables, I thought you said they were normalised?

regards Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[Bastian]

I’m not sure what you mean by “normalized”. I’m only trying to join on the productnumber column, which (I thought) should be a 1:m, but it’s coming up as a 1:1.

Description, unitmeasurement, weight, etc. repeat in both tables because I thought I needed to do that in order to add those records. I’m probably going about it entirely wrong… What do I need to do to be able to enter a list of records from tblpartslist into tblinventory?

In other words –

I want to enter invoice information into a form based on tblinventory. Each invoice has a list of parts. I need to pull that info from tblpartslist.

Sorry for the confusion – I’m new to this! Thanks so much for your patience.

 

[Bastian]

Well, thanks anyway.

 

[KenReay]

Hi

I am am in UK, so I might be on a different time zone to you, possibly this is reason for delay in replying.

You need to get your design right before heading off into forms etc, otherwise you will just deeper in the mire.

1. Normalisation, This is too big a subject to go into here, but briefly it is the process of refining a Relational Database design so that certain conditions are met, for example absence of repeating columns, absence of redundant data. There may be reasons why you have certain data repeated in your table design for example if the table Inventory is a list of items ordered, you may wish to repeat the price, since you will want the price at the time of Order, not necessarilly the price now, but at first sight you table structure should be:


tblinventory:

invoicenumber – long integer (primary key)
salesordernumber – long integer
jobname – text, 20
dateshipped – date
productnumber – long integer
price – currency
qtyordered – long integer
qtyshipped – long integer
netamount – currency
invoicetotal – currency ??????


tblpartslist:

productnumber – long integer (primary key)
description – text, 50
unitmeasurement – text, 4
price – currency
weight – double

By having a query which joins the two tables on PArtNumber, you will have a recordset which includes description, unit measure, there is no need to repeat them in both tables (unless there is a good reason as in the case of Price outlined above).

Also you will note I have a ????? against invoicetotal this is because I suspect this is a derived field based on qty * price or (heaven forbid) a sum of all of the items in a given Invoice. If it is a derived field, do not store it in the table, calculate it as a nd when you want it either by having a calcualated column in the query, or using a group by query with a Sum().

2. If you have table PartsList with Primary Key PartNumber and Table Inventory with Primary key InvoiceNumber and a column PartNumber with same type as PartNumber in table Partslist and you go into relationships window and make a relationship between PartsList.PartNumber and Inventory.PartNumber then YOU WILL get a 1 (at PartsList side) to many (at Inventory side) relationship.

Regards
Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[Bastian]

Thanks so much for replying – I assumed you had given up on me for my cluelessness. *sheepish grin*


I set the tables and relationship up the way you have outlined. Yay!!

The invoice total isn’t a derived field – it’s just the total that I enter from the invoice. I decided it would be better to do it that way, just to make sure that the records I keep in the database match up with the actual invoices no matter what.

SO. I created the parts list AutoLookup query, and it functions exactly as it should. Hoorah! The only problem is this:

If I create a form based on tblInventory, with a subform based on the Autolookup query, how do I enter the QtyOrdered, QtyShipped, and NetAmount for each part ordered?

I would like to use the product number to look up each record, but I also need to enter quantities for each part when they’re ordered.

Also, many parts will be ordered repeatedly.

I know this is probably just child’s play for you… Thank you so much for sticking with me! I appreciate it so much.

 

[Bastian]

Pssst! Hello? Anyone?