Solved

Access Relationships

Posted on 2011-03-09
18
302 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 84
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 84
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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 84
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

735 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