Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • 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.
Access-Tables.jpg
0
ITHelper80
Asked:
ITHelper80
1 Solution
 
RgGray3Commented:
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




0
 
TirocupidusCommented:
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]
0
 
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?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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