Solved

Access Relationships

Posted on 2011-03-09
18
257 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
  • 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75
ID: 35090883
standby ...
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now