Solved

Access sql

Posted on 2014-09-29
4
548 Views
Last Modified: 2014-10-01
There are three tables – table specific to item, table specific to distributor and a join table that has existing combination of item and distributor. Usually use fill in the jon table to with the possible (existing combination of item and the distributor level where they are sold. I need a union query oof some sort that returns a Cartesian product of the two tables – items Distributor join table and distributor List table  - so that for example –
If item T23 was listed with only distributor D01 in the join table >> then the query returns a total of 5 rows – with the possible combination of that item and all the other distributor list but puts 0 for the price. See attached image and DB.
Thank you
superDB.accdb
superDB-QueryResults.jpg
0
Comment
Question by:Rayne
[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
  • 2
  • 2
4 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 40351067
You don't need a Union.  A union query stacks lists on top of each other so all rows from tblA are returned followed by all rows from tblB.  Both tblA and tblB MUST have the same format.  A join compares two tables and returns matches (inner join), rows from tblA without rows in tblB (left join), rows from tblB without rows in tblA (right join), all rows from tblA matched to every row in tblB (cross join or Cartesian product).

So to create a Cartesian Product, add the two tables to the QBE.  Select the columns you want from each table.  Do NOT draw a join line.
0
 

Author Comment

by:Rayne
ID: 40351145
A and B have different formats because they are different tables
0
 

Author Comment

by:Rayne
ID: 40351147
so i cant change thier column order or numbers
0
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40352201
I said you don't need a union so you don't need to worry about column format or order.  You need a query that creates a Cartesian Product.  Add both tables to the QBE.  Do NOT draw a join line.  Select the columns you want from either table.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

751 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