INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

best way to join - brain block

best way to join - brain block

(OP)
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.

RE: best way to join - brain block

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

RE: best way to join - brain block

(OP)
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.

RE: best way to join - brain block

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)

RE: best way to join - brain block

(OP)

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)



RE: best way to join - brain block

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

RE: best way to join - brain block

(OP)
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.!

RE: best way to join - brain block

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

RE: best way to join - brain block

(OP)
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).

RE: best way to join - brain block

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

RE: best way to join - brain block

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

RE: best way to join - brain block

(OP)
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

RE: best way to join - brain block

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

RE: best way to join - brain block

(OP)
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.

RE: best way to join - brain block

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

RE: best way to join - brain block

(OP)
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.

RE: best way to join - brain block

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

RE: best way to join - brain block

(OP)
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.

RE: best way to join - brain block

(OP)
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.

RE: best way to join - brain block

(OP)
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.

RE: best way to join - brain block

I typically use a single form to edit a single table. A main form has the record source of the main/parent table and the subform is bound to the related child table.

Duane
Hook'D on Access
MS Access MVP

RE: best way to join - brain block

(OP)
Thanks!! Can I make it look as if it were one form? The look right now has arrows along the left side and two bars at the bottom. One more question can I get them to "save" or "update" all in one click when they are finished filling in the information then go to a new record? Thanks!

RE: best way to join - brain block

This has no meaning to me

Quote (quackslikeaduck)

has arrows along the left side and two bars at the bottom
. Can you explain the relevance?

What are your record sources?

What are your tables/fields and relationships?

Have you looked at any sample applications with forms and subforms?

Duane
Hook'D on Access
MS Access MVP

RE: best way to join - brain block

(OP)
Hi Thanks! My record sources are listed above. I think I've used just about all fields. When my users are through filling out the out the purchase orders I would like them to have a way to update or save the information to the tables then if need be move on to a new form to fill out another purchase order or close the application.

RE: best way to join - brain block

TblPurchaseOrder should be the record source of your main form. tblPurchaseOrderItems should be the record source of your subform. The link master/child should be ProductCodeNum.

ProductCodeNum in tblPurchaseOrder should be an autonumber or you need to make sure the values are all unique.

Duane
Hook'D on Access
MS Access MVP

RE: best way to join - brain block

(OP)
I believe that's the way I have it setup with exception of the ProductCodeNum isn't an autonumber. That's why I couldn't figure out what to join on in the beginning. Even the PO field is a DMax value so that all PO's are in order. Do you another way that I could have done it. I didn't want to link all ID's to one another. Do you think it's wrong the way it is now?

I think it's okay for now but I need to read up on the customization of the form and the buttons to save, go to new record or close.

RE: best way to join - brain block

Feel free to explain "Even the PO field is a DMax value".

What do you mean by "link all ID's to one another"?

If your record sources are like I suggested, you shouldn't have any issues editing records.

Duane
Hook'D on Access
MS Access MVP

RE: best way to join - brain block

(OP)
What I meat by "Even the PO field is a DMax Value" is that it isn't an autonumber so I couldn't link on it with all records. I think I'll leave it as is for now because I believe that my record sources are as you suggested. Should I make it so that all ProcuctCodenNum is indexed and "No Duplicates"?

RE: best way to join - brain block

Quote (quackslikeaduck)

Should I make it so that all ProcuctCodenNum is indexed and "No Duplicates"?
Five days ago you stated it was the primary key of TblPurchaseOrder. I hope you didn't change this.

Duane
Hook'D on Access
MS Access MVP

RE: best way to join - brain block

(OP)
I didn't change it. So far things are going good. I've been able to add my form and subforms. Still going with a few minor snags but I'm sure I'll overcome them.

RE: best way to join - brain block

I have a similar issue and have yet to be able to figure out how to join these two tables. I have one table that has several pieces data in one column and I need to join only some of this data to another table I created with the relative data.

Little background: we receive confirmations of products received via email, there are around 1000 emails received monthly and I am going through them individually. I've imported the emails into access and need to join on the subject line as the receiving sends confirmations saying: confirmation # 12345 or confirmation#12345. I have a separate table with the order numbers that I'm looking for confirmations on....hope this makes sense.

RE: best way to join - brain block

tootyfruity,
Welcome to Tek-Tips. Can you provide some sample data with table and field names and how you want them joined. It sounds like you are storing multiple values in a single field which is typically not a good idea.

Duane
Hook'D on Access
MS Access MVP

RE: best way to join - brain block

Yes that is exactly what's happening. I've inherited this project unfortunately. The idea is to join on the subject line which contains: Order Confirmation # 638917872-001 Partial Order/$23.74 RECEIVED

I have a separate table with all order numbers. I'd like to pull just the order number from this field and nothing else, to join on my other table.

RE: best way to join - brain block

Does every field value contain the same beginning of "Order Confirmation #" so you can create a new field and update it with part of the subject line field?

Duane
Hook'D on Access
MS Access MVP

RE: best way to join - brain block

No, not every email will begin with 'Order Confirmation'. This is entered manually by the receiver. Isn't there logic in access to pull a specific range of characters after the # sign?

RE: best way to join - brain block

tootyfruity,
There are lots of string functions that can be used. Instr(), Mid(), Left(), and Right() should get you what you want. If after searching Help and/or the web you don't find a solution, you can create your own string functions.

Feel free to come back with about 5-10 values from the field and what values should be extracted from each.

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close