Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-09-2019, 08:38 AM   #1
elly.khanlar
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
elly.khanlar is on a distinguished road
URGENT: How to create a query based on composite key from two table

Hi everyone,
I'm trying to create a query by joining two tables based on their composit key. I have tried to build the composit key for two tables. However, in the design grid the name of composit key for each table doesnt appear so I can join them?

elly.khanlar is offline   Reply With Quote
Old 10-09-2019, 08:40 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,047 Times in 1,028 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: URGENT: How to create a query based on composite key from two table

Hi. That's correct. You don't link tables using index. Instead, you use fields to link them. So, try linking all the fields you used in the index in your query.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-09-2019, 09:25 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,348
Thanks: 10
Thanked 2,270 Times in 2,222 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: URGENT: How to create a query based on composite key from two table

Your phrasing around composite key makes me feel like you don't fully understand what one is. A composite key is the combination of multiple fields in one table that makes the record unique:

https://www.javatpoint.com/sql-composite-key

For example a SalesPersonID and SalesDate could make a composite key in a table that lists all sales:

DailySales
SalesPersonID, number, foreign key to SalesPerson table
SalesDate, date, date of sale
SalesQuantity, number, number of sales for that salesperson on that date

In DailySales, every SalesPersonId and SalesDate permutation is unique. There will never be multiple records that have both the same SalesPersonID and SalesDate values. That is a composite key.

Perhaps you can demonstrate your issue with data.

plog is offline   Reply With Quote
Old 10-09-2019, 09:38 AM   #4
elly.khanlar
Newly Registered User
 
Join Date: Oct 2019
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
elly.khanlar is on a distinguished road
Re: URGENT: How to create a query based on composite key from two table

From table A here are fields
Delivery location,location desc,material,material desc,volume,month,quarter
the composite key for this table is Delivery location and Material


From table B here are fields,
Delivery location,location desc,material,material desc,month,origin

the composite key for this table is delivery location and material


by joining these two tables composite key, I would like to create a table that will add origin to table A based on the delivery location and material
elly.khanlar is offline   Reply With Quote
Old 10-09-2019, 09:56 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,566
Thanks: 50
Thanked 1,047 Times in 1,028 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: URGENT: How to create a query based on composite key from two table

Quote:
Originally Posted by elly.khanlar View Post
From table A here are fields
Delivery location,location desc,material,material desc,volume,month,quarter
the composite key for this table is Delivery location and Material


From table B here are fields,
Delivery location,location desc,material,material desc,month,origin

the composite key for this table is delivery location and material


by joining these two tables composite key, I would like to create a table that will add origin to table A based on the delivery location and material
Hi. Just a guess (without anything to use for testing), but you could try something like:
Code:
SELECT B.Origin
 INTO NewTable
FROM TableB B
 INNER JOIN TableA A
ON B.[Delivery Location]=A.[Delivery Location]
  AND B.Material=A.Material
(untested)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-09-2019, 10:14 AM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: URGENT: How to create a query based on composite key from two table

When you don't know how to write SQL, try the QBE. It is drag and drop and pops up dialogs for joins.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create combobox based table column without messing the table settings up? behedwin General 1 11-16-2017 07:29 AM
Create Datesheet based on Any Query or Table MLUCKHAM Forms 0 10-25-2013 12:47 AM
Urgent Help needed with table create Query. hardrock Queries 3 08-07-2007 06:22 AM
How to create composite primary key mech55 General 1 09-09-2005 02:51 PM
[SOLVED] Subform based on table with composite primary keys johank General 0 11-27-2002 04:08 AM




All times are GMT -8. The time now is 11:43 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World