• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 801
  • Last Modified:

Query to merge and compare data

I have 5 tables I am trying to query against. tblSalesDetail, tblProdCode, tblBillingLine, tblTransactionCodes, and tblSuffixes. TblSalesDetail has the following fields: Ledger Number, Legal Entity, Account Number, Prod Line, Inter-Company, Reference, Transaction Code, Billing Line, Invoice Number, Item, GO Number, Warehouse Code, Style Number, QTY, CA TE, Prod Code, Prod Code Description, Amount I need to create a query that will pull in the following data from the following. tblBillingLine/Billing Line Description, tblSuffixes/Warehouse Description, and  tblTransactionCode/TRN Code Description. I also need the query to look at field ProdCode in tblSalesDettial and tblProdCode, if they match the Prod Code Description field from tblProdCode should be placed beside the corresponding Prod Code field within tblSaleDetail. If this is confusing please let me know and I will try to clarify.
1 Solution
What is it you want to do...
What can't you do

Simply pull the required tables on the query
Pull down the fields you want down to the grid...

If you have never created a query in access, let me know and I will talk you through the process

It looks like your relationships are set up between incorrect fields in that screenshot. Below is a query that should list everything you want, but it will not store the Prod Code Description in tblSalesDetails. I'm not sure if you want to do that, or just display the data. (I just typed this up, so hopefully there are no syntax errors in it)

SELECT [Ledger Number], [Legal Entity], [Account Number], [Prod Line], [Inter-Company], Reference, [Transaction Code], [Billing Line], [Invoice Number], Item, [GO Number], [Warehouse Code], [Style Number], QTY, CA TE, tblSalesDetail.[Prod Code], IIf(tblSalesDetail.[Prod Code] = tblProdCode.[Prod Code], tblProdCode.[Prod Code Description], '') AS CalculatedProdCodeDescription, Amount, tblBillingline.[Billing Line Description], tblSuffixes.[Warehouse Description],  tblTransactionCodes.[TRN Code Description] FROM (((tblSalesDetail LEFT JOIN tblBillingLine ON tblSalesDetail.[Billing Line] = tblBillingLine.[Billing Line Code]) LEFT JOIN tblSuffixes ON tblSalesDetail.[Warehouse Code] = tblSuffixes.[Warehouse Code]) LEFT JOIN tblTransactionCodes ON tblSalesDetail.[Transaction Code] = tblTransactionCodes.[Transaction Code]) LEFT JOIN tblProdCode ON tblSalesDetail.[Prod Code] = tblProdCode.[Prod Code]
ITHelper80Author Commented:
I need the query to pull  in the following data from the following tables/fields and display them for a report: tblBillingLine/Billing Line Description, tblSuffixes/Warehouse Description, and  tblTransactionCode/TRN Code Description and all fields from tblSalesDetail.

In tblProdCode I have to fields "Prod Code" and "Prod Code Description" what I am trying to do is compare the "prod code" fields from tblSalesDetail and tblProdcode; if they match then the "Prod Code Description" (for that prod code) should be placed in a description column. DOes that make more sense?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now