Query help - Filtering with "Lookup & Relationship" fields

[pleopard777]

Access 365 ... I'm a newbie of course ... I'm frustrated because I cannot seem to create the query I want, hopefully this is a simple thing but I've googled a lot and have not a found suitable remedy. Here's a description of the issue, step by step ... all database fields are short strings with the exception of Lookup & Relationship fields but the fields in the associated tables are short strings.

1 - I created a new blank database named "Action Tracker"
2 - I created a table named "Projects" with fields "Project Name" and "Abbreviations"
3 - I created a table named "Statuses" with fields "Open", "Closed", "In Progress", "Cancelled", "On Hold", and "Recurring"
4 - I created a table named "Priorities" with fields "ASAP", "High", "Medium", "Low", "Long Term", and "Top"
5 - Finally I created a master table named "Actions" with several fields including the following Lookup & Relationship fields:

"Project" - associated with "Projects" table
"Status" - associated with "Statuses" table
"Priority" - associated with "Priorities" table

6 - All works great so far. I have populated the database with roughly 20 records
7 - Now I create a Simple Query with the Query Wizard and select the "Actions" table and most of its fields
8 - All looks good so far
9 - I want to filter the records by one of the Lookup & Relationship fields so I do the following:
a - Switch to design view
b - For the "Status" field I enter "Open" in the criteria box
c - Click the !Run and get an error dialog stating "Data type mismatch in criteria expression"
d - Same happens for all Lookup & Relationship fields (Project,Priority)
e - Works great for all Short Text fields



Surely there is a simple fix that I am unaware of, no?

[isladogs]

Just to clarify: are you using lookup fields at table level?
If so, this is a very bad idea and most developers would tell you to avoid them.
My article explains some of the reasons: http://www.mendipdatasystems.co.uk/t...lds/4594445135

I have no idea what you mean by relationship fields

[pleopard777]

I believe so ... when I create the Actions table and add a field I click the "Click to Add" pointer then select the "Lookup & Relationships". A dialog comes up that walks me through the process. I select "I want the lookup field to get the values from another table or query" then select the Priorities, Projects, or Statuses table that I want, then select the field I want from the available fields list. I'll try to upload the database to a server tonight for reference, can't do that here at my job. Thanks

[Micron]

Do not pass Go, do not collect $200 - in other words, arrest your project now. Will only mention each point once but it might apply more than once.
1 - don't use spaces or special characters in object names, save for perhaps underscore ( _ )
2 - You seem to be missing a PK field, or you are using meaningful data as a PK
3 - this is spreadsheet design; do not do so in a db
5 - do not use lookup fields; use lookup tables and form controls

Do read all of these and employ as much as possible. Pay particular mind to normalization and the evils of lookup fields.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[isladogs]

Yes. That is a table level lookup field. As already stated it is a very bad idea. Read my article and the links in that for reasons why.
Instead you should create a separate table for your lookup values

[Micron]

I think the tables are there:
"Statuses" with fields "Open", "Closed", "In Progress", "Cancelled", "On Hold", and "Recurring"
...
fields including the following Lookup & Relationship fields:
...
"Status" - associated with "Statuses" table

Status table is obviously not normalized but that's just one of a few/many.

Or maybe I misinterpreted something.
P.S. I chimed in because I looked at the time and thought you might be getting your beauty sleep

[ssanfu]

It sounds like you started designing the dB in Access without first designing it using paper. whiteboard, cardboard or, my favorite, my picture window.

3 - I created a table named "Statuses" with fields "Open", "Closed", "In Progress", "Cancelled", "On Hold", and "Recurring"
4 - I created a table named "Priorities" with fields "ASAP", "High", "Medium", "Low", "Long Term", and "Top"

Isn't "Open", "Closed", "In Progress", "Cancelled", "On Hold", and "Recurring" the data?? You have a field named "Status" and you select "Open", "Closed", "In Progress", "Cancelled", "On Hold", or "Recurring".
Same goes for "ASAP", "High", "Medium", "Low", "Long Term", and "Top". They are DATA! Should Not be field names.


Look at it like this:
Suppose in 9 months Milton Moron convinces the Grand Poo-Bah that 3 more priorities must be added: Critical, Urgent and Important.
Should you have to redesign your tables, queries, forms, reports and code just to add the 3 priorities?

[isladogs]

Just to say that I agree completely with the issues listed by @Micron and @ssanfu.
Taken together with my points about lookup fields at table level, you really do need to pause and then redesign your application from scratch.

[pleopard777]

Wow, lot's of great input here, thanks a pile folks!

I've completely re-wrote the database using your guidance as best I can.

You can download the new database here

I still have the issue of not being able to filter the query using values from the Priorities table, guidance?

Thanks!
P

[pbaldy]

Post 9 was moderated, I'm posting to trigger email notifications.

[pleopard777]

Post 9 was moderated, I'm posting to trigger email notifications.

Not sure I understand. Did I post something I should not have posted? I see no changes

[pbaldy]

Not sure I understand. Did I post something I should not have posted? I see no changes

No, you didn't do anything wrong, and the post is exactly as you made it. The forum software will automatically moderate posts from new members that contain links. It's an anti-spam thing. A moderator will review and approve the post if it isn't spam. Once you get a few more posts it won't happen anymore.

[pbaldy]

Oh, and my post was made to trigger email notifications to the people who were helping you so they would know you had replied.

[davegri]

I still have the issue of not being able to filter the query using values from the Priorities table, guidance?

You still have lookup fields in your Actions table. Remove the lookups at the bottom, leaving the fields at the top as is:



After removing them the table data looks like this:



Then you can filter the query like this:



Edit: If you want the modified copy of the db, let me know and I'll upload it for you.

[Micron]

Please upload your db here. Some won't access files from an external link; some cannot because they say they are at work (I've often wondered, aren't they supposed to be working?). You copy the db, compact it then zip it. See "how to attach files" at the top if you need help.