MS Access Query fails to resolve when using multiple values

[Andy_CCF]

Issue:
I am developing a simple issue tracking database and havehit a stumbling block that I’m not sure how to resolve. Have tried severalapproaches using queries, sql statement etc but still not working. I may have torethink how I am doing this but hoping someone may be able to address the issueas it stands, though if a more elegant way of doing it happy to implement that.

Scenario:
A table called tblUsershas a field called Access that is alookup to a table called tblCategoryand allows for multiple values to be stored (one to many). In essence this issaying which category(s) of “issue” the user is allowed to see.

A simple msgbox test in code shows that this is correctlystoring the values selected in the following format

In turn, each issue can only have a single category(one to one) which is stored in a field called Category in table tblGMPIssuesand is also populated from a lookup to the tblCategorytable.

So far so good ….

I then have a query called qryUserIssues that should show all issues from the table tblGMPIssues that are a) “Open” (status= 1) and that b) match any of the categories that the user is permitted toview.

I can get this to work with a single value i.e. as it standsquery prompts for input and returns expected results i.e. lists records that match that single value, but I can’t work out the syntax to get the criteria toaccommodate multiple values. For example, in above scenario our user should beallowed to see 4 different category or calls “1, 2, 3, 4”

Tried using various JOINS, tried assigning to variables andusing a LIKE criteria but can’t seem to get the syntax right.

If anyone could let me know if this can be done and if sohow as it’s driving me nuts J

All help and suggestions gratefully received.

[ranman256]

youve got too many joins. you dont need a relationship to join say, a tPerson table to a tState table.
The state table FEEDS the tPerson (address) but does not need a relationship. These lookup tables do not enter into the relationships.
A person does need a relationship to a tPhone table. One person can have many phones. (home,cell,work) and becomes Parent /child relation.

tPhones tbl
-------------
PersonID *
PhoneID *
phone#

this way it becomes 1 to many. one field does not have multiple values.

[ssanfu]

My $0.02.....

In "tblUsers", "Password" is a reserved word
In "tblGMPIssues", "Type" is a reserved word. Plus it is not very descriptive. "Type" of what?
In "tblSeverity", "Description" is a reserved word. Plus it is not very descriptive. "Description" of what?

You have 6 tables with the PK field named "ID". How confusing is this???


I would get rid of any and all Lookup FIELDS.
I would get rid of any Multi-value fields.
Most experienced Access programmers/developers do not use Lookup FIELDS, Multi-value FIELDS, nor Calculated FIELDS.

With Multi-value fields, I think you will have an extremely hard time getting the data you want from a query.

[Andy_CCF]

Hi Steve and Ranman,

Thank you both for quick response, you'll have to forgive my lack of Access knowledge. I've only ever used databases Access, MySQL , SQL as a data store and all coding has previously been web based and even this was years ago .

The relationships and links have all been auto generated by Access as part of creating those fields as LOOKUPS. So I will implement changes this afternoon on naming convention, fields descriptions, unique PK names and removing lookup fields.

Regards the multi-value fields though i'm still very confused.

A user can have 'access' for want of a better word to one or more Issue Categories
An Issue can only ever be assigned to a single Category

Currently on the frmUserDataEntry form there is a drop down fed by lookup to tblCategory (ID, Category) that allows them to select the types of issues they can see. How do I replicate this but avoid storing the selection as multi-values?? Still not sure i'm explaining this properly.

Regards,
Andy

[Andy_CCF]

Further to last reply made all of the above recommended changes (better naming convention, unique PK names, changed fields to stop using LOOKUP, etc) and after a bit of time spent updating vba code to reflect new table / fields names all works as before (albeit still with multi values at this point) with one exception. The following line of vba code no longer works.

Originally field was called Status in tblGMPIssues

lblStatus.Caption = (DLookup("StatusDescription", "tblStatus", "[StatusID]=" & Status.Value))

Changed the Status to issue Status and table to tblIssues and updatedthe line of code to be:

lblStatus.Caption = (DLookup("StatusDescription", "tblStatus", "[StatusID]=" & IssueStatus.Value))

and it fails on the IssueStatus.Value

Is this a syntax

[orange]

The database you are developing is in support of your business. Can you describe to readers, in plain English, (no jargon and no Access /database constructs) - describe your business just as you would to an 8 yr old?

Using the old Who, what, when, where, how much and how often sort of approach.

[Andy_CCF]

Simply put a department within the organisation wishes to track issues that staff be it hygiene specialists, engineers, qa's, etc will be required to fix.

These issues can be categorised into three major categories Hygiene, Engineering or Non-Conformance and each has several sub categories i.e. Area A, Area B, Area C, Area D, etc.

Additionally an issue will be accorded a "Type", i.e. Glass, Mental, Fabric, Hygiene, etc these types are only available to some of the above categorisations.

As mentioned above the person who may be doing the job to fix the issue could one in one of several other departments and as such they should only be allowed to see the jobs that are pertinent to their job role/department.

The business process operates in the following way the Supervisors of this system will raise an issue detailing the nature of the issue the impact to business, photographic evidence of issue, etc.

On a daily basis a number of people will then check this system for unresolved issues and attempt to fix them. When they do so they then update the system to say what they have done and can again attach photographic evidence if required. These staff submit their fix for review to the Supervisors of this system.

The Supervisors then review the work done by staff and can add comments, if they are happy they can close the issue otherwise they can ask the person who did the work to make further changes. and the process repeats until Supervisor is happy and they close the issue.

The Supervisors are able to view all the calls on the system whereas other staff will be limited.

Hope this is simple enough as a business process.

[ssanfu]

Would you post your dB for analysis? Just a few records to see how things work. Change any sensitive data (use cartoon characters as emp names, etc)