Solved

How to Merge Join Dlookup 2 different Tables in MS Access with a Query or Add Text To The Front of a Existing Table Field Record

Posted on 2016-09-22
10
50 Views
Last Modified: 2016-09-23
Ok I have (May Not Be The Right or Best Way) 2 different tables set up currently. One Is called
ParentProductTable

Open in new window

and the other is called
ProductVariantDescriptionTable

Open in new window

.  The Table
ParentProductTable

Open in new window

keeps track of all the products and is the basic parent form of the product. There is a field called "
ProductNameTitle

Open in new window

which is the basic name and title of the product. There is 4 different variations of each product in the
ProductVariantDescriptionTable

Open in new window

  The Variations are called NSOP,NSSP,USOP,USSP . Each one is a record in the field
ConditionCode

Open in new window

in the table
ProductVariantDescriptionTable

Open in new window

. I just am thinking this is the bestway to set this up currently. What I am trying to do is perform a query to make one of the
ConditionCode 

Open in new window

record names go in front of the
ProductNameTitle

Open in new window

Example would be ProductNameTitle = Yellow Bus 20 seat .......   ConditionCode = NSOP  = NSOP Yellow Bus 20 Seat

I then will have to do this the same way for the other 3 remaining NSSP,USOP,USSP  
ConditionCode

Open in new window


I am having a hard time getting it to work and only thing so far I have had happen is the Dlookup return just the first record of each table with code
ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable") & " " & DLookUp("ProductNameTitle","ParentProductTable")

Open in new window


I don't know if Nesting Dlookups would work exactly and how to code multiple nesting Dlookups.


Please and thank you to all!

1-example.jpg
0
Comment
Question by:Dustin Stanley
  • 7
  • 3
10 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Step back a little.  There is a bigger design issue.

You also need to change the definition of the parent table to move the "prefixed" columns ( NSSP,USOP,USSP ) to the appropriate rows in the variant table.  

Always be wary of prefixed/suffixed names even if the prefixes/suffixes are not numeric.  These fields constitute a repeating group and so violate first normal form.

Once the table is properly normalized, you would just join the tables and get back a row for each type.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
Ok I kinda get what you are saying but I am not 100% of some of the wording you used (Lingo). What I really need is to be able to make a report (CSV File) to export to other websites. I don't exactly need to store each one of my ProductNameTitle as a Prefix in the Databse itself. Just a file to report to another website. So Export out of my database as a variant prifixed and then import into another website. I hope that part makes sense. I have studied of database normalization several times for days and thought I had it right. Could you please help me by giving me some tips or examples specifically to my database so it makes more sense to me. If I don't store that data within in my database as variants with prefixes/suffixed is my database then normalized? Making a report for what I need is the correct way?? Right? I thank you very much for your time.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The DLookup() is returning the value for the first record because you are not using any criteria to pick a specific row.

ProductVariantNameTitle: DLookup("Condition Code Description","ProductVariantDescriptionTable", "ConditionCode = 'NSOP'") & " " & ProductNameTitle

You want the ProductNameTitle from the current record so no dLookup() is required for that.  The problem is there is nothing in the ParentProductTable that indicates what row you want in the description table.  Therefore, I hard-coded the value for one of the ConditionCodes.  You have to know ahead of time what value to use or add code to lookup all four of them.

As I said, this design is incorrect.  You are probably better off moving the descriptions back into the main table.  You have separate fields for NSOP,NSSP,USOP,USSP which is 100% wrong.  It isn't "wronger" to move the descriptions back and have four of them in the parent table also.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
I thank you for your advice and time like you wouldn't believe. It really means a ton to me. I apologize for any ignorance or misunderstanding I may have or give.  Sometimes my thought pattern is a little different then most and that's is why I asked for specific examples and that code you supplied was exactly what I was looking for but I had to change it a little to:

ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'NSOP'") & " " & [ProductNameTitle]

I totally get that now there not being a criteria in the code for the table to understand. Thank you. I do have a few questions from your last 2 posts so I can clear the air and move forward. As this database when i am done will help me beyond belief.

1: How would I (Believe correct terminology) Nest the Dlookup code in 1 query to run all condition codes in one run? Would it be

ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'NSOP'") & " " & [ProductNameTitle]
ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'NSSP'") & " " & [ProductNameTitle]
ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'USOP'") & " " & [ProductNameTitle]
ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'USSP'") & " " & [ProductNameTitle]

2: You said move the condition codes back into the main table ParentProductTable. Would I just make a new field called ConditionCodes. (I am lost there) Every product has four different Conditions and You then said I can't use different fields for different conditions.

3. You said "You have separate fields for NSOP,NSSP,USOP,USSP which is 100% wrong." Are you talking about the ProductVariantDescriptionTable or the ParentProductTable? In the ProductVariantDescriptionTable  the are 4 separate records for the conditions not fields and the ParentProductTable has separate fields for the quantity and price. I am trying to steer clear of making a record for every condition. Just have the parent product in the database to slim things down.

I would like to share a photo of a previous database application I was using and it is very slow so I am trying to build my own. This is how I have based my thinking and strategy from. In the photo the inventory graph is at the bottom. Also I have added some other photos to see if it can help clarify some things. Again thank you for EVERYTHING!
Inventory graph at the bottomdb-1.jpgdb-2.jpg
This is what I am trying to create as a report CSV file that I can export from my parent products database. Every parent will have the four condition variants like in this CSV file photo. I would then use this CSV file to import to other websites.

DB3.jpg
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
Mushing everything into a single table is not simplifying your task, it is making it harder.  Right now you have only four options for the toy.  What if you add a fifth?  Think about how that would affect your forms/reports/queries/code.  Then read about first normal form and try to understand why it is important to remove the two repeating groups - toys and images.  Each should be in a separate table.  Once you make that separation, you can add a new product by adding a row in a table and you will not have to change ANYTHING else.  This is the power of using a relational database and where it deviates from a spreadsheet.

ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'NSOP'") & " " & [ProductNameTitle]

Doesn't make any sense.  The DLookup is returning the value of your criteria.  If you are trying to prefix the description with the four-character code, then do this:
ProductVariantNameTitle: "NSOP  " & [ProductNameTitle

If you need all four fields in the same query, then the the fields need unique names.

ProductVariantNameTitleNSOP: "NSOP  " & [ProductNameTitle]

The only reason to use the DLookup() at all is go get the non-key value stored in the table NOT the "key" value.

The lookup table isn't doing anything for you as you have it implemented.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Dustin Stanley
Comment Utility
Ok just as an update I have figured out part of this. I had to make 4 queries. 1 for each condition of NSOP, NSSP, USOP,USSP. The queries for each was
 ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'NSOP'") & " " & [ProductNameTitle]

Open in new window


Replacing NSOP with NSSP USOP USSP for each one.

NSOP CONDITION
ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'NSOP'") & " " & [ProductNameTitle]

Open in new window


NSSP CONDITION
ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'NSSP'") & " " & [ProductNameTitle]

Open in new window


USOP CONDITION
ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'USOP'") & " " & [ProductNameTitle

Open in new window


USSP CONDITION
ProductVariantNameTitle: DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'USSP'") & " " & [ProductNameTitle]

Open in new window


So that is 4 different Queries.

I then had to make a 5th Query as A UNION QUERY . Here is a link for putting multiple queries on a single table result  https://support.office.com/en-us/article/Use-a-union-query-to-combine-multiple-queries-into-a-single-result-1f772ec0-cc73-474d-ab10-ad0a75541c6e"

You basically have to create a blank query and then go into SQL view and copy each query 1-4 into the SQL view of the 5th Query. Deleting the semicolon on the end of each query and putting the word UNION on the next line down. Until you get to the end where you don't delete the semicolon and don't put the word UNION in. The link walks you step by step. My code in SQL ended up looking like this.

SELECT ProductVariantDescriptionTable.ConditionCode, ParentProductTable.ProductNameTitle, DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'NSOP'") & " " & [ProductNameTitle] AS ProductVariantNameTitle
FROM ProductVariantDescriptionTable, ParentProductTable
UNION
SELECT ProductVariantDescriptionTable.ConditionCode, ParentProductTable.ProductNameTitle, DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'NSSP'") & " " & [ProductNameTitle] AS ProductVariantNameTitle
FROM ProductVariantDescriptionTable, ParentProductTable
UNION
SELECT ProductVariantDescriptionTable.ConditionCode, ParentProductTable.ProductNameTitle, DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'USOP'") & " " & [ProductNameTitle] AS ProductVariantNameTitle
FROM ProductVariantDescriptionTable, ParentProductTable
UNION
SELECT ProductVariantDescriptionTable.ConditionCode, ParentProductTable.ProductNameTitle, DLookUp("ConditionCode","ProductVariantDescriptionTable","ConditionCode = 'USSP'") & " " & [ProductNameTitle] AS ProductVariantNameTitle
FROM ProductVariantDescriptionTable, ParentProductTable;

Open in new window



This code has worked for the most part but for some reason I have 4 of each. I just need one of each. I wrote this post and as you replied back so I hadn't seen it yet. But here is what I have made in the results. But I need to get just one line for each condition code. db4.jpg
0
 

Author Comment

by:Dustin Stanley
Comment Utility
OK tell me if I am wrong here or not. You think I am trying to put all the conditions into one table? IF I understand correctly. I am trying to get a report (If that is the correct way to call it) Of all the quantities and prices of all the variations we have. So I can then make a CSV file to use on another website and import it there. The Each toy would have a single name and each toy would have a single image URL for each Image. Like Image URL1 and so on. I use the same Image for Each Condition. So there would only be a single image URL1 in the actual database. No redundancy. The only time there would be a redundancy is in the Report. The Union Query. That is just because the websites I import into have to have a single record for each condition. I can not put all variations in one record on THEIR site. I hope that makes sense and maybe I am not getting your view correctly. Thanks for everything. This is helping a ton.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
Just perfect! Yes I just needed to prefix my titles. And I figured it out completely. I created 4 different queries and made a UNION Query to run them all. Thank you so much. One last thing Can you please tell me how the coding

ProductVariantNameTitle: "NSOP  " & [ProductNameTitle]

Open in new window


Would be different to suffix it with the code at the end? THANK YOU SO MUCH! I am very happy though that I went all out of my way and started wrong as this helped me learn alot of new things!!
0
 

Author Comment

by:Dustin Stanley
Comment Utility
Got It

Suffix
ProductVariantSKU: [ProductParentSKU] & "NSSP"

Open in new window


Prefix
ProductVariantSKU: "NSSP" & [ProductParentSKU]

Open in new window

0
 

Author Closing Comment

by:Dustin Stanley
Comment Utility
THANK YOU!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now