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

x
?
Solved

Access Relationships

Posted on 2011-03-09
18
Medium Priority
?
320 Views
Last Modified: 2012-05-11
I have a table in Access that contains our firm structure, such as which locations numbers reports into a region and then which region reports into a division.

In addition, I have query where it contains the locations number.  How do I pull the regions and then the divisions into the query from the table that contains the firm structure?
0
Comment
Question by:ArisaAnsar
[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
  • 8
  • 7
  • 3
18 Comments
 
LVL 85
ID: 35088393
You'll have to give more info about your structure. Can you post details of how those tables are all related?
0
 

Author Comment

by:ArisaAnsar
ID: 35088561
I attached a sample of the database.  I want to be able to add the appropriate division, region and complex name from the table called Complex Structure to the qry E-Delivery.

These are all test data on the file.
Relationship-Database.zip
0
 
LVL 85
ID: 35088799
So you want to relate the query with the "Complex" table on the Branch field that you've generated in the E-Delivery Query? If so, you'll have to "cast" the Branch column to a Numeric value, since the field "Branch" in the Complex table is a Numeric field. To do that, change that Branch field in the E-Delivery QUERY to this:

Branch: CDbl(Left([E-Delivery]![AcctNumber],3))

Save that query, and then build a NEW query. Add the E-Delivery and the Complex table, relate those two on the Branch field (drag the Branch field from one and "drop" it on the Branch field of the other), and then add the fields you want your new query to show to the Design grid.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 75
ID: 35088949
You can do this in one query ... using this SQL:

SELECT Left([E-Delivery]![AcctNumber],3) AS Branch, [E-Delivery].AcctNumber, [E-Delivery].Statements, [E-Delivery].Prospecturs, [E-Delivery].[T-1099], [E-Delivery].Confirms, (SELECT [Complex Structure].Division FROM [Complex Structure] WHERE [Branch] = Left([E-Delivery]![AcctNumber],3)) AS Division, (SELECT [Complex Structure].Region FROM [Complex Structure] WHERE [Branch] = Left([E-Delivery]![AcctNumber],3)) AS Region, (SELECT [Complex Structure].Complex FROM [Complex Structure] WHERE [Branch] = Left([E-Delivery]![AcctNumber],3)) AS Complex
FROM [E-Delivery];


see attached mdb.
Relationship-Database-MX01.zip
0
 
LVL 75
ID: 35089071
Actually, this is better:

SELECT Left([E-Delivery]!AcctNumber,3) AS Branch, [E-Delivery].AcctNumber, [E-Delivery].Statements, [E-Delivery].Prospecturs, [E-Delivery].[T-1099], [E-Delivery].Confirms, [Complex Structure].Division, [Complex Structure].Region, [Complex Structure].Complex
FROM [E-Delivery], [Complex Structure]
WHERE ((([Complex Structure].Branch)=CLng(Left([E-Delivery]![AcctNumber],3))));

See attached and qry E-DeliveryMX

Relationship-Database-MX02.zip
0
 

Author Comment

by:ArisaAnsar
ID: 35090010
DatabaseMx,
Thanks very much.   I cannot get my example to work as you provided in Database -MX02.   The example you provided in Database-MX01 works PERFECTLY.  Thanks.  

What is the CLng function?  Isn't that to round an  integer?
0
 
LVL 75
ID: 35090036
Database-MX01 ... well, that may be slow with a LOT of records with 3 sub queries.

CLng() in this case is converting that portion of the AcctNumber ... which is Text (but seen as Numeric) to a Long Integer ... to match the Branch Number in the Complex Structure table.  Otherwise, a Type Mismatch would occur.

MX-02 should work for you ... it works for me.  What is going on ?

fyi ... off line now until circa 21:00 PDT ...

mx
0
 

Author Comment

by:ArisaAnsar
ID: 35090048
I'm gtting a "type mismatch in expression" when I use MX-02.
Thanks.
0
 

Author Comment

by:ArisaAnsar
ID: 35090448
I got it.  Based on your explanation above, I realized that in my real database the columns were set as text so I did not need the CLng function.    

When I run the query, I noticed that some of my records are dropped.  I know the reason is because I don't have a corresponding complex, region and division on the Complex Structure database.  What do I need to do inorder to include that data and just leave those cells blank?
0
 
LVL 75
ID: 35090883
standby ...
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 35091368
ok ... You can *run* the SQL below, but it will not display in the query designer (save as qry E-DeliveryMX3):

SELECT  Left([E-Delivery]!AcctNumber,3) AS Branch, [E-Delivery].AcctNumber, [E-Delivery].Statements, [E-Delivery].Prospecturs, [E-Delivery].[T-1099], [E-Delivery].Confirms, [Complex Structure].Division, [Complex Structure].Region, [Complex Structure].Complex
FROM [E-Delivery]

LEFT JOIN [Complex Structure] ON Left([E-Delivery].[AcctNumber],3)= [Complex Structure].Branch

And this is even better than the previous version ... because of the JOIN.  I just couldn't remember the syntax.  This s/b FAST for large recordsets.

Note that I also changed the data type in the test daat to text to remove the CLng()  as you did.

mx
Relationship-Database-MX03.zip
0
 
LVL 75
ID: 35091372
btw ... Version 1 using the 3 subqueries also works with the new scenario - missing entries in the Complex Structures table.

mx
0
 
LVL 85
ID: 35093773
In some cases, if you are experiencing performance slowdowns, you might consider using a stored Query to produce your "Branch" value from the E-Delivery table. The Left() function can take some time to run, and in some cases saving the query can provide a more performant "subquery".

You might also consider using stored queries as your Subqueries, since in some cases those can provide better performance metrics. It's a bit more trouble to maintain that mx's single query statement, so there is a tradeoff.
0
 

Author Comment

by:ArisaAnsar
ID: 35096173
I'm getting the following message
"Join Expression Not Supported."

What does that mean?
0
 

Author Closing Comment

by:ArisaAnsar
ID: 35096397
Its working now.  I'm not sur what I was doing wrong before.   Thank you very, very much
0
 
LVL 75
ID: 35096886
"What does that mean?"
Well, it means what I was referring to when I said:

"ok ... You can *run* the SQL below, but it will not display in the query designer (save as qry E-DeliveryMX3):"

In other words, you cannot create the JOIN directly in the query designer.  Access does not allow it. BUT ... you can create it in the SQL window of the query designer, and execute the SQL.  Kind of silly I know, but that's just how it works.

mx
0
 

Author Comment

by:ArisaAnsar
ID: 35096936
Thanks again.  Appreciate it very much.
0
 
LVL 75
ID: 35096999
You are welcome ...

mx
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

721 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