Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query to merge and compare data

Posted on 2008-06-19
3
Medium Priority
?
795 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 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