Solved

Query to merge and compare data

Posted on 2008-06-19
3
789 Views
Last Modified: 2011-10-19
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
Comment
Question by:ITHelper80
3 Comments
 
LVL 11

Expert Comment

by:RgGray3
ID: 21825357
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
 
LVL 4

Accepted Solution

by:
Tirocupidus earned 500 total points
ID: 21825466
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 21825562
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

920 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

15 Experts available now in Live!

Get 1:1 Help Now