Tables are possessed? Cannot filter for item "1" filters item "3".

[Gina Maylone]

Good morning all!

Short of giving up all together, I am once more at your mercy. I'm attaching a stripped down db for review. This is an estimating database, I am trying to use 3 tables to create a blank quote report: Categories, BidItemCosts, TempJobInfotable (this is populated from a List Box on the customer form).
When I try to filter the table columns in BidItemCosts for "1" I get a "no current record" error, and then get anything except the "1's"! Check out BlankQuoteQuery for a clear example of this.

Your knowledge is always appreciated!!MyZip.zip

[orange]

Most people will advise you to NOT use lookups at the table field level.
This is the common reference.

I really don't understand your query.
And there is no form in the attached database.
"this is populated from a List Box on the customer form"????

It would be helpful if you gave a short description of the purpose of the query in plain English.

I'm guessing that your company does something along this line:
We are in the landscaping and construction business. We do Jobs that can be identified by Category and each Category has a number of individually priced Items. Your objective is to create a "Quote/Bid" for a Customer related to a Job.

A Customer requests a Job

1 Job can involve 1 to Many Items
Each Item has a Price
A Quote/Bid is an estimated cost of Items required to complete the Job

(again this is a guess of what you're trying to do--could be totally incorrect)

[JamesDeckert]

Some other things I noticed.
If you display the CategoryID from all 3 tables in your query, they don't match.
Your query links the tables different than your relationships are set up.
I don't understand what the temp table is for, I change some of the IDs in it and got more weirdness in the query with the IDs not matching.

[Gina Maylone]

Orange,

You are absolutely correct as to what I'm trying to get at. Categories are an overall service provided, BidItemCosts are the individual items under those categories, and the TempJobInfotable was so I could create a type of union query to pull the info into the bid report. And I only attached a stripped down version of the db for simplicity's sake, so no forms or other tables not required for this query.

In English I want to get all of the items under the category selected, so like you said, if the category is Roof all the items required for completing that bid would be listed in the report.

Thank you for the reference - I have never seen that before. And I will take it to heart. Thank you for your time!!

[Gina Maylone]

Thank you James for noticing the weirdness - I thought of corruption first off and started over and got the same thing. I think I will take Orange's advice and remove the lookup at the table level - I never knew that was a no-no. Learn something new every day!

Thank you for your time!
Gina