Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Access Relationships

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
ArisaAnsar
Asked:
ArisaAnsar
  • 8
  • 7
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'll have to give more info about your structure. Can you post details of how those tables are all related?
0
 
ArisaAnsarAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
ArisaAnsarAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
ArisaAnsarAuthor Commented:
I'm gtting a "type mismatch in expression" when I use MX-02.
Thanks.
0
 
ArisaAnsarAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
standby ...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
btw ... Version 1 using the 3 subqueries also works with the new scenario - missing entries in the Complex Structures table.

mx
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
ArisaAnsarAuthor Commented:
I'm getting the following message
"Join Expression Not Supported."

What does that mean?
0
 
ArisaAnsarAuthor Commented:
Its working now.  I'm not sur what I was doing wrong before.   Thank you very, very much
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
ArisaAnsarAuthor Commented:
Thanks again.  Appreciate it very much.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome ...

mx
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now