Solved

MS Access query returns multiple rows, cannot use Distinct as it must be updateable

Posted on 2008-10-19
6
960 Views
Last Modified: 2011-10-19
Hello -

I've been struggling with this for a week and I'm stuck. Maybe I'm just approaching this all wrong. Any help would be appreciated.

I created a form which will allow users to search/filter the data by various fields and display the companies which meet the specified criteria. I've created a query to pull all my data, but it is returning multiple records when a company has multiple WorkTypes and/or ProjectTypes.

For example:

Company            WorkType      ProjectType
---------------------------------------------------------------
AAA Plumbing      Plumbing      Residential
AAA Plumbing      Plumbing      Commercial


Using DISTINCT returns a single record for each company, but then it's no longer updateable.

Note: Results must be updateable. However, the users will only be updating fields in tblCompany so I do not need to display anything from any of the other tables. I just need to search them.

Here is an abbreviated table structure.

tblCompany
------------------------
CompanyID_PK as Int
CompanyName as String
....tons of other fields


tblCompanyWorkTypes
------------------------
CompanyID_PK as Int
WorkTypeID_PK as Int


tblCompanyProjectTypes
------------------------
CompanyID_PK as Int
ProjectID_PK as Int


tblWorkTypes
------------------------
WorkTypeID_PK as Int
WorkDesc as String (e.g. Roofing, Plumbing, etc.)


tblProjectTypes
------------------------
ProjectTypeID_PK as Int
ProjectDesc as String (e.g. Residential, Commercial, etc.)

My query is as follows:

SELECT tblCompany.CompanyID_PK, tblCompany.Comments, tblWorkTypes.WorkDesc, tblProjectTypes.ProjectDesc
FROM tblProjectTypes RIGHT JOIN ((tblCompany LEFT JOIN (tblWorkTypes RIGHT JOIN tblCompanyWorkTypes ON tblWorkTypes.WorkTypeID_PK = tblCompanyWorkTypes.WorkTypeID_PK) ON tblCompany.CompanyID_PK = tblCompanyWorkTypes.CompanyID_PK) LEFT JOIN tblCompanyProjectTypes ON tblCompany.CompanyID_PK = tblCompanyProjectTypes.CompanyID_PK) ON tblProjectTypes.ProjectTypeID_PK = tblCompanyProjectTypes.ProjectTypeID_PK;




Again, any help would be very much appreciated.
0
Comment
Question by:wgclark
  • 3
  • 2
6 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 22753369
I don't follow you.  If you only want to update info in tblCompany, why do it from a query involved with four other tables?  
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 22753380
For update purposes, reduce the RecordSet query to just the company table.  Now you have an updateable query where each company is represented just once.

There are lots of ways to find a company from the recordset.  One way is to use the built-in form navigation controls and menus to page through the companies. Or you use the button wizard to generate navigation buttons.  

There are also a variety of ways to "search" for a company based on WorkType or whatever.  I will describe a method that uses filters to limit the recordset to companies of interest.

1.  Add a combobox control to your form.
2.  Keep the control source blank (unbound)
3.  Make the rowsource like this:  
     "Select WorkTypeID, WorkDesc from tblWorkTypes"
4.  Make column count = 2
5.  Make bound column = 1
6.  Make column width = 0;

Now you have a dropdown for choosing the WorkType.  The last step is to filter the RecordSource so that it reflects the WorkType selection.

1. In the AfterUpdate event of the combobox, apply a filter to the company form:
    Me.filter="CompanyID_PK in (Select CompanyID_PK
           From tblCompanyWorkTypes
           where workTypeID=" & THECOMBOBOX.value
    Me.filteron=True

Now, when you select a company in the combobox, only the matching companies will appear on the form and you can navigate between them.  You can elaborate on the technique to filter on other columns, clear the filter, etc.  It may take a little tweaking to get the form's behavior to work in a way that is intuitive, but this should get you headed in a good direction.









0
 
LVL 42

Expert Comment

by:dqmq
ID: 22753388
Sorry, forgot the trailing paren here:

1. In the AfterUpdate event of the combobox, apply a filter to the company form:
    Me.filter="CompanyID_PK in (Select CompanyID_PK
           From tblCompanyWorkTypes
           where workTypeID=" & THECOMBOBOX.value & ")"
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:wgclark
ID: 22753426
In order to find the correct records in tblCompany the user will need to search on WorkDesc and ProjectDesc. Maybe I'm being overly complex about this. Here is the relationships.
 
 

relationships.jpg
0
 

Author Comment

by:wgclark
ID: 22753559
My situation is a little more complex than I led on, but this was exactly what I needed.
    Me.filter="CompanyID_PK in (Select CompanyID_PK
           From tblCompanyWorkTypes
           where workTypeID=" & THECOMBOBOX.value & ")""
 
Thanks dqmq!!!
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22753669
>My situation is a little more complex than I led on

That's almost always the case... Happy Day
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

792 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