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]

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

 

[quackslikeaduck]

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!

 

[dhookom]

This has no meaning to me

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

 

[quackslikeaduck]

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.

 

[dhookom]

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

 

[quackslikeaduck]

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.

 

[dhookom]

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

 

[quackslikeaduck]

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"?

 

[dhookom]

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

 

[quackslikeaduck]

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.

 

[tootyfruity]

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.

 

[dhookom]

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

 

[tootyfruity]

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.

 

[dhookom]

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

 

[tootyfruity]

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?

 

[dhookom]

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