best way to join - brain block

[quackslikeaduck]

I'm trying to make joins of the relationships in my database. However I can't seem to join anything and have it work out. Meaning that I don't see all of the data in the form. I don't mind creating a new form but I honestly don't see anything I can join on. I have 4 tables with several fields in each. If there is a way I can upload a copy of my table I will.

 

[dhookom]

You can possibly use LEFT or RIGHT JOINs which include all the records from one table and where they match in the other. Just double-click the join line in the query designer and select the appropriate option (either 2nd or 3rd).

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

That's my problem. I don't have anything to join on. There aren't many numeric records in the table. When you say that they are equal if there are only 2 records out of 500 that actually have numbers in them then they won't show right. I've tried to join both ways but I need to come up with something that joins 4 tables.

 

[MajP]

You do not need to post your database and people are often too busy to download it. Just describe you tables and pertinent fields. Normally something like this. Describe how they relate (in plain english), describe the type of information (in plain english).

TblEmployee (a table holding information on employees)
EmployeeID (primary key, autonumber)
LastName (text field)
FirstName (text field)

TblProjects (a table holding information on projects)
projectID (primary Key, text field)
ProjectName (text)
...
projectLead (foreign key linked to the employee table)

 

[quackslikeaduck]

TblPurchaseOrder (a table holding information on items being ordered)
ProductCodeNum (primary key, number)
LastName (text field)
FirstName (text field)
To (text field)
Address (text field)
City_State_ZIP (text field)
DeptNum (number field)
ProductCodeNum (number field)
RequestedBy (text field)
DateRequired (Date/Time)
HowShip Terms (text field)
Date (Date/Time)
PO# (number field)
Total (currency field)

tblPurchaseOrderItems
ID (Primary Key, autonumber)
ProductCodeNum (number field)
QuantityOrdered (number field)
TotalInventory (number field)
QuantityReceived(number field)
RequestedItems (number field)
PricePerUnit (number field)
TotalInventory (number field)
QuantityReceived(number field)

tblItemList
ID (Primary Key, auto number)
Description
$$/unit Supplier
UOM

tblSupplierList
ID (Primary Key, auto number)
SupplierName (text field)
Address (text field)
City/State (text field)

 

[dhookom]

How do you get data into your tables if they aren't related? Isn't tblItemList.ID related to tblPurchaseOrderItems.RequestedItems? Who created the tables and forms?

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

I didn't create it. It was handed to me with no real joins. They added data manually. The tables are not fully populated. The original form worked but they were all joined on the id but no foreign keys all joined on primary keys. I broke up the tblPurchaseOrder and made the tblPurchaseOrderItems. I would think that I could join the tblItemList.ID and tblSupplierList.Supplier but it doesn't seem to be working. I may be looking at it too close because I have been sitting here for weeks 12+ hours a day trying to get a good join. Each time I change the joins I either get a blank form (after redoing the controls on each field) or the whole form shows up as #NAME.

All/any help is appreciated.!

 

[dhookom]

I would expect ProductCodeNum to be the primary and foreign key field between tblPurchaseOrder and tblPurchaseOrderItems. Did you look at the tables to see if there were any matching values? Didn't you suggest you created tblPurchaseOrderItems?

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

I did look and they do have only 2 matching records. I did have them joined on ProductCodeNum. I just created the joins based on the information you gave me above. Half of my form is now filled in but the other half is still showning #NAME. It looks like all of the fields that are showing #NAME are from the tblPurchaseOrderItems. I did create tblPurchaseOrderItems because there were multiple fields in the tblPurchaseOrder (QuantityOrdered (1-5), QuantityonHand (1-5), etc..., etc...) so I took them out and put them in a table of their own (tblPurchaseOrderItems).

 

[dhookom]

ProductCodeNum in the new table should be long integer and you should have matched it's value to your original. Apparently you didn't do that now nothing matches.

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

I checked and it is a long integer in all tables.

 

[quackslikeaduck]

Right now I have the following joins:

tblPurchaseOrderNumber.ProductCodeNum related to tblItemList.ProductCodeNum in one to many enforce referential Integrity Cascade update related fields

tblPurchaseOrder.ProductCodeNum related to tblPurchaseOrderItem.ProductCodeNum in one to many enforce referential Integrity Cascade update related fields


tblPurchaseOrder.ProductCodeNum related to tblSupplierList.ProductCodeNum in one to many enforce referential Integrity Cascade update related fields

tblItemList.ID related to tblPurchaseOrderItem.ProductCodeNum in one to many enforce referential Integrity Cascade update related fields

 

[dhookom]

tblItemList.ProductCodeNum isn't possible since you don't have that field in that table. The same is true with tblSupplierList.ProductCodeNum.

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

I'm really sorry I did add that field to all tables. I've been working with this nonstop but haven't made any changes to the tables other than adding that field to all tables as that is the only numeric field that they could have in common. I'm not sure where to go at this point. It looks like all joins are as should be.

Is there any way to test that the joins are as should be.

I don't have a join between tblItemList and tblSupplierList as none of the joins I've tried actually work. How do I know if my joins are right? Only half of the information on the form is populated. It looks like anything from tblPurchaseOrderList is still not showing up on the form. Marked with #NAME. I've never had a problem like this that has consumed so much of my time and brain burnout.

 

[dhookom]

Focus on two tables first. If you can't fix these two, don't try to fix anything else.

Apparently you had an un-normalized PO table with repeating groups of columns and [ProductCodeNum] as the primary key. When you "broke up the tblPurchaseOrder and made the tblPurchaseOrderItems" you should have made sure every record in tblPurchaseOrderItems had the appropriate [ProductCodeNum] stored in a long integer field. If you didn't do this, your records are not related and can't be joined. You would then create a form bound to tblPurchaseOrder and subform bound to tblPurchaseOrderItems with the Link Master/Child properties of the subform set to ProductCodeNum. This makes sure any new records in tblPurchaseOrderItems are related to a record in tblPurchaseOrder.

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

Okay. I did set ProductCodeNum to long integer in all tables. All data in that field are the same. I now have my form but have never created a sub form so I'm off to do that. Thanks so much for taking the time.

 

[dhookom]

quackslikeaduck,
You might want to pick up a good beginners book and/or search the web for basic Access tutorials. Crystal Long has some great ones on youtube.

Duane
Hook'D on Access
MS Access MVP

 

[quackslikeaduck]

Believe it or not I used to do this like 10 years ago. I haven't had to do it and when this is over hopefully won't have to do it again. I do remember creating the subform within the form years ago but yes, basically back to ground 0.

Thanks for hanging in here with me. I probably won't have time to do the form today until later but will repost after I do.

 

[quackslikeaduck]

I was able to create all of my fields but now when I try to change anything I just hear a ding noise. I keep getting error: "recordset not updateable".

I really hope that this is an easy fix because I've been sitting here since last night redoing everything.

 

[quackslikeaduck]

Okay I did get the form to work with two of the table fields in the main form and two in subforms. Is that the way it will have to stay? It's fine if it is but if this indicates something else that needs to be done to get them all on one form then I don't want to do anything further until I know different.