Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-20-2019, 01:06 AM   #1
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 593
Thanks: 288
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Good approach to writing advanced queries

Hi Guys,

i have to get CustomerTopologyID based on Temptable:



I can not simple create joins like here because i have:



generally i am breaking this query into 2 steps: first where i am getting CutomerID and TopologyID, and in second query i am left join it to CustomerTopology table to get CustomerTopologyID.

It is good approach?
I do not how to join this in one step?
What about performance of database if i will add to many not truly necessarry queries?

Best,
Jacek
Attached Images
File Type: jpg Screenshot_11.jpg (75.7 KB, 104 views)
File Type: jpg Screenshot_12.jpg (74.2 KB, 104 views)

jaryszek is offline   Reply With Quote
Old 09-20-2019, 01:14 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,469
Thanks: 68
Thanked 2,721 Times in 2,606 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Good approach to writing advanced queries

why do you need to have another table?
all info can be extracted from temp_soft, customer and topology.
dont over over normalized, leading to abnormal structure.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
jaryszek (09-20-2019)
Old 09-20-2019, 01:16 AM   #3
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 593
Thanks: 288
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Good approach to writing advanced queries

thank you arnelgp.

I have unfortunately this table because this is the part of bigger model.
And i have to somehow join it to my tempTable and question is about approach, will you do the same or write it in one query somehow? How to do this via wizard?

Jacek

jaryszek is offline   Reply With Quote
Old 09-20-2019, 01:22 AM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,611
Thanks: 89
Thanked 1,494 Times in 1,410 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Good approach to writing advanced queries

Just change the inner joins to outer joins in the same direction as the others.
Galaxiom is offline   Reply With Quote
Old 09-20-2019, 01:30 AM   #5
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 593
Thanks: 288
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Good approach to writing advanced queries

thank you very much Galaxiom but it didnt work, i have still error:



Please help,
Jacek
Attached Images
File Type: jpg Screenshot_13.jpg (87.4 KB, 101 views)
jaryszek is offline   Reply With Quote
Old 09-20-2019, 01:34 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,469
Thanks: 68
Thanked 2,721 Times in 2,606 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Good approach to writing advanced queries

add another temp_software to your layout.
reverse all those arrows.
topology linked to orig temp_soft
customer linked to the other temp_soft.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
jaryszek (09-20-2019)
Old 09-20-2019, 01:47 AM   #7
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,419
Thanks: 162
Thanked 1,734 Times in 1,704 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Good approach to writing advanced queries

I'm with Arne - this isn't needed as you are already storing this in the first table.
Also your temp table is storing the name not the FK from your two look up tables.

Simply move the FK to your table on the right and you have your junction table with the additional information.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

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.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
jaryszek (09-20-2019)
Old 09-20-2019, 02:03 AM   #8
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 593
Thanks: 288
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Good approach to writing advanced queries

ok thank you once again!

Quote:
Also your temp table is storing the name not the FK from your two look up tables.
yes because i am getting string names...

Quote:
add another temp_software to your layout.
DONE

Quote:
reverse all those arrows.
DONE

Quote:
topology linked to orig temp_soft
customer linked to the other temp_soft.
and this i am not understanding.
Can you explain more?



Best,
Jacek
Attached Images
File Type: jpg Screenshot_14.jpg (89.0 KB, 91 views)
jaryszek is offline   Reply With Quote
Old 09-20-2019, 02:09 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,469
Thanks: 68
Thanked 2,721 Times in 2,606 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Good approach to writing advanced queries

link customer to Other temp_soft
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-20-2019, 02:56 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,264
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Good approach to writing advanced queries

I can see why you are doing it - you want to identify the customer topology ID from the customer and topology names.

You need two queries to do what you require.

you can go either way, but the easiest is to link topology, customers and customertopologies together with ordinary (inner) joins and return customertopologyID and the two names. Save this query

then create another query left joining your temp table to the other query on the names.

You can do this in one query if required - provide the sql you have and I can show you how
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-20-2019, 03:05 AM   #11
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 593
Thanks: 288
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Good approach to writing advanced queries

Hmm this is very strange:



i am getting error here but some of relationships have number 2, some 3.

I do not know why.
Whe i have situation like here:



it is working but adding topologyId will throw an error as above.
Maybe i will upload example database ?

Best,
Jacek
Attached Images
File Type: jpg Screenshot_15.jpg (86.3 KB, 82 views)
File Type: jpg Screenshot_16.jpg (67.8 KB, 80 views)
jaryszek is offline   Reply With Quote
Old 09-20-2019, 03:09 AM   #12
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,264
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Good approach to writing advanced queries

see my post
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-20-2019, 05:54 AM   #13
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 593
Thanks: 288
Thanked 1 Time in 1 Post
jaryszek is on a distinguished road
Re: Good approach to writing advanced queries

Thank you CJ_London,

i am attaching test database.
Can you please show me 2 methods? With 2 queries and one query?
Attached Files
File Type: accdb Test.accdb (880.0 KB, 6 views)
jaryszek is offline   Reply With Quote
Old 09-20-2019, 06:04 AM   #14
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,264
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Good approach to writing advanced queries

qry1
Code:
SELECT CustomerTopologyID,CustomerName, TopologyName
FROM tblTopologies INNER JOIN (tblCustomers INNER JOIN tblCustomerTopology ON tblCustomers.CustomerID = tblCustomerTopology.CustomerIDFK) ON tblTopologies.TopologyID = tblCustomerTopology.TopologyIDFK
qry2
Code:
SELECT CustomerTopologyID, Temp_SoftwareComponentsOses.*
FROM Temp_SoftwareComponentsOses LEFT JOIN qry1 ON (Temp_SoftwareComponentsOses.CustomerName = qry1.CustomerName) AND (Temp_SoftwareComponentsOses.TopologyName = qry1.TopologyName)
single query
Code:
SELECT CustomerTopologyID, Temp_SoftwareComponentsOses.*
FROM Temp_SoftwareComponentsOses LEFT JOIN (SELECT CustomerTopologyID,CustomerName, TopologyName
FROM tblTopologies INNER JOIN (tblCustomers INNER JOIN tblCustomerTopology ON tblCustomers.CustomerID = tblCustomerTopology.CustomerIDFK) ON tblTopologies.TopologyID = tblCustomerTopology.TopologyIDFK)  AS qID ON (Temp_SoftwareComponentsOses.TopologyName = qID.TopologyName) AND (Temp_SoftwareComponentsOses.CustomerName = qID.CustomerName);
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
jaryszek (09-22-2019)
Old 09-20-2019, 07:27 AM   #15
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,469
Thanks: 68
Thanked 2,721 Times in 2,606 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Good approach to writing advanced queries

see change in structure of the tables
I delete one junction.
you are creating unnecessary table.
customer table can directly connect to topology.
no need to mediate between customer and topology.
see query1.

check your title.
Attached Files
File Type: accdb Test (5).accdb (1.07 MB, 4 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
jaryszek (09-22-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Advanced queries by form DK22 Queries 4 08-04-2013 05:15 AM
Use of Access Queries, Best Approach Steve R. Theory and practice of database design 4 10-15-2006 12:30 PM
Help writing queries marleyuk Queries 0 02-28-2006 02:43 AM
I've never been any good at queries! Autoeng Queries 8 05-01-2003 03:09 AM
[SOLVED] Advanced filters/queries ayc Queries 2 12-03-2002 04:13 AM




All times are GMT -8. The time now is 04:16 AM.


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