SQL Where Clause Not Working

[Bdenn]

Hello,


Having a little issue with the following SQL:

SELECT DateStamp, DiscrepancyID, DiscrepancySubject, DiscrepancyStatus, DiscrepancyPriorityFROM tblDiscrepPriority, tblDiscrep
WHERE DiscrepancyPriority="Archive"
ORDER BY DateStamp;

I want a query that displays anything that has been selected as Archive will be populated when this query is ran.

Now issues:
This Archive drop down menu lives in tblDiscrep. But I used the lookup wizard under datatype in design view. So what this does is I have a table called tblDiscrepPriority and anything that is entered into this table will show up in the dropdown menu in table tblDiscrep under the column DiscrepancyStatus. When query is ran currently it puts all items from tblDiscrep into the query and puts Archived next to it all when really right now I only have one item that is displayed as Archived.

What exactly is the SQL to make this work? Do I need to call up both tables in the SQL?
The Query Fails and does not run if I only use the tblDiscrep Table.

[Bdenn]

2 Other Photos dealing with the issue.

[ItsMe]

Do I need to call up both tables in the SQL?

I don't have a lot of experience with lookup fields and the wizard that creates them. I seem to remember an option within the wizard for storing the values in a table. If you did this, I would guess it is a matter of bringing the table onto the design window, creating a join, adding the field to the grid, adding criteria to the field, and deleting the field that is the lookup field in tblDiscrep.

Having said that, if it is not too late, I would consider getting rid if that lookup field.

[Bdenn]

I don't have a lot of experience with lookup fields and the wizard that creates them. I seem to remember an option within the wizard for storing the values in a table. If you did this, I would guess it is a matter of bringing the table onto the design window, creating a join, adding the field to the grid, adding criteria to the field, and deleting the field that is the lookup field in tblDiscrep.

Having said that, if it is not too late, I would consider getting rid if that lookup field.

The problem is I need to use this drop down because I need other users to use nothing else but what is available in the box "DiscrepancyStatus"

[orange]

A few things:

-With relational database, every table should have a PrimaryKey which uniquely identifies each and every record in the table.
-You should avoid the Access Lookup Wizard to add lookup(data type) fields within tables. see this for details. This 'FEATURE" is specific to Access and most developers will tell you to avoid it for several reasons.
-Your SQL represents a Cartesian Product which I don't think you want.

I see you have 3 posts on this forum, so I'm going to suggest this tutorial on database and table design. This normally takes 30-45 minutes and will teach you design concepts. It will also help you with relationships between tables. You have to work through it to get the benefits.

Good luck.

[ItsMe]

You could try using an index as criteria for the field that is the lookup field. Instead of typing Archive in the Criteria field type 1 or maybe 0. I think it starts as 1 Index.

[Bdenn]

Alright, so is it possible to have "DiscrepancyStatus" column have a drop down menu with only the items I want. As in this information will be referenced by another table and only display whats in the other table?

Also, all of my tables have primary keys so we should be good there.

Im guessing this will working fine (SQL) if I disable the lookup wizard but whats the work around?

[ItsMe]

Not following your questions, exactly.

The alternative to Lookup Fields is to store the stuff in another table. If you need a pulldown/dropdown effect, you can achieve this in a form using a Combobox control.

The Combobox control will display stuff that is defined by its RowSource. Insert SQL into the combo's RowSource to retrieve the desired records.

[Bdenn]

I will take a look at that.. so do the same thing. Kill the wizard and use a form to reference the other table.

[orange]

If you have distinct values for DiscrepancyStatus, you can create a reference table:

eg

tblDiscrepancyStatus
DiscrepancyStatusId autonumber Primary Key
DiscrepancyStatusDesc text

with values ( Taken from your post )
1 Weather Conditions
2 Low Priority
3 High Priority
4 Archive
5 Site Work


Then in your tblDiscrepancy you would add a field (FK to tblDiscrepancyStatus) called DiscrepancyStatusId (numeric, long integer)

Then your query SQL would be along the line of :

SELECT tblDiscrepancy.DateStamp, tblDiscrepancy.DiscrepancyID, tblDiscrepancy.DiscrepancySubject, tblDiscrepancyStatus.DiscrepancyStatusDesc, tblDiscrepancy.DiscrepancyPriority
 FROM tblDiscrepancyStatus  INNER JOIN tblDiscrepancy
ON tblDiscrepancyStatus.DiscrepancyStatusID = tblDiscrepancy.DiscrepancyStatusID
WHERE tblDiscrepancy.DiscrepancyPriority="Archive"
ORDER BY DateStamp;

I have a feeling you could use lookup/reference tables for other attributes as well.
For example, Discrepancy priority.

It is confusing to the reader, and possibly highlights a problem with your terminology, when the same term "Archive" is used as a value of two or more fields/attributes. Here you use "archive" for DiscrepancyStatus and
DiscrepancyPriority.

An interesting exercise for you would be to identify each table and each attribute with a 1 -2 line description.Doing so would help clarify what is a Status versus Priority. Readers do NOT know your environment and its jargon as well as you, so what may be obvious to you is less so to readers. Describing your terms (documenting your tables and attributes) often helps to focus your thinking and design. This is especially rue when working with multiple users and/or developers. The team has to be on the same page...

Also,in Access the form is the appropriate interface for all users. No one should be working directly with tables.
Good luck with your project.

[ItsMe]

... use a form to reference the other table.

In a sense, yes. The combo control can behave like its own Query.

[Bdenn]

Sorry about that.
as you said the definition was a bit off. so obviously the code you provided did not work

The two table are:
1. tblDiscrep
A. DiscrepancyID
B. DiscrepancySubject
C. DiscrepancyType
D. DiscrepancyStatus
E. DateStamp
F. DiscrepancyDiscription
G. OffAir

2.tblDiscrepPriority
A. PriorityID
B. DiscrepancyPriority

*Keep In Mind
I messed up labeling stuff here and DiscrepancyPriority & DiscrepancyStatus are the same thing so when I drop down the menu for DiscrepancyStatus it displays everything from DiscrepancyPriority.


I changed it up a bit but still not running yet.

SELECT tblDiscrep.DateStamp, tblDiscrep.DiscrepancyID, tblDiscrep.DiscrepancySubject, tblDiscrepPriority.DiscrepancyPriority, tblDiscrep.DiscrepancyStatus FROM tblDiscrepPriority  INNER JOIN tblDiscrep
ON tblDiscrepPriority.DiscrepancyPriority = tblDiscrep.DiscrepancyID
WHERE tblDiscrep.DiscrepancyPriority="Archive"
ORDER BY DateStamp;

So what am I messing up bad in that SQL?

[June7]

Do not work directly with tables and queries for data entry/edit - use form.

Do not build lookups that have alias in tables - build combo and list boxes on form.

What data is stored in DiscrepancyPriority - numeric key or literal descriptive text?

If you want to provide db for analysis, follow instructions at bottom of my post.