Query from Form

[ria4life]

SO i'm using a form to filter data via a query:

I have this code in there and it works perfect:

SELECT tblEmployeeMaster.[Charge RC], tblEmployeeMaster.[First Name], tblEmployeeMaster.[Last Name], tblEmployeeMaster.HDQMGT0084, tblEmployeeMaster.HDQMGT0085
FROM tblEmployeeMaster
WHERE (((tblEmployeeMaster.[Charge RC])=[Forms]![DetailQueryForm]![rcsearch] Or [Forms]![DetailQueryForm]![rcsearch] Is Null) AND ((tblEmployeeMaster.HDQMGT0084)=[Forms]![DetailQueryForm]![F84] Or [Forms]![DetailQueryForm]![F84] Is Null) AND ((tblEmployeeMaster.HDQMGT0085)=[Forms]![DetailQueryForm]![F85] Or [Forms]![DetailQueryForm]![F85] Is Null));



However, after a few searches, i start getting zero data only to realize that access added a bunch of additional information:

SELECT tblEmployeeMaster.[Charge RC], tblEmployeeMaster.[First Name], tblEmployeeMaster.[Last Name], tblEmployeeMaster.HDQMGT0084, tblEmployeeMaster.HDQMGT0085
FROM tblEmployeeMaster
WHERE (((tblEmployeeMaster.[Charge RC])=[Forms]![DetailQueryForm]![rcsearch]) AND ((tblEmployeeMaster.HDQMGT0084)=[Forms]![DetailQueryForm]![F84]) AND ((tblEmployeeMaster.HDQMGT0085)=[Forms]![DetailQueryForm]![F85])) OR (((tblEmployeeMaster.HDQMGT0084)=[Forms]![DetailQueryForm]![F84]) AND ((tblEmployeeMaster.HDQMGT0085)=[Forms]![DetailQueryForm]![F85]) AND (([Forms]![DetailQueryForm]![rcsearch]) Is Null)) OR (((tblEmployeeMaster.[Charge RC])=[Forms]![DetailQueryForm]![rcsearch]) AND ((tblEmployeeMaster.HDQMGT0085)=[Forms]![DetailQueryForm]![F85]) AND (([Forms]![DetailQueryForm]![F84]) Is Null)) OR (((tblEmployeeMaster.HDQMGT0085)=[Forms]![DetailQueryForm]![F85]) AND (([Forms]![DetailQueryForm]![rcsearch]) Is Null) AND (([Forms]![DetailQueryForm]![F84]) Is Null)) OR (((tblEmployeeMaster.[Charge RC])=[Forms]![DetailQueryForm]![rcsearch]) AND ((tblEmployeeMaster.HDQMGT0084)=[Forms]![DetailQueryForm]![F84]) AND (([Forms]![DetailQueryForm]![F85]) Is Null)) OR (((tblEmployeeMaster.HDQMGT0084)=[Forms]![DetailQueryForm]![F84]) AND (([Forms]![DetailQueryForm]![rcsearch]) Is Null) AND (([Forms]![DetailQueryForm]![F85]) Is Null)) OR (((tblEmployeeMaster.[Charge RC])=[Forms]![DetailQueryForm]![rcsearch]) AND (([Forms]![DetailQueryForm]![F84]) Is Null) AND (([Forms]![DetailQueryForm]![F85]) Is Null)) OR ((([Forms]![DetailQueryForm]![rcsearch]) Is Null) AND (([Forms]![DetailQueryForm]![F84]) Is Null) AND (([Forms]![DetailQueryForm]![F85]) Is Null));




I am confused as to what's going on ...any help will be greatly appreciated.
I have attached an image of all the additional "OR" criteria added.

Thanks in advance.

 

[Ranman256]

Your query is so convoluted it could be any 1 of the criteria.
Reduce the criteria to 1 item ,then test.
Continue adding until it works.

 

[plog]

I think those 2 sets of codes are logically equivalent.

Can you provide some sample data to demonstrate where the first code produces results but the second code doesn't? Or vice versa?

 

[JHB]

I think plog is right.
Try the following to get rid of the bunch of additional information, (yes I know it sound like a joke), create a new query switch to SQL-view, put in the SQL-string you show in the first part, copy the WHERE part (<Ctrl>+C) then delete the WHERE part of the query, switch to Design view and back to SQL-view and paste the WHERE part back in again (<Ctrl>+V).
Remember to delete the ; after "... FROM tblEmployeeMaster;"

 

[ria4life]

Thanks All.

I have uploaded the sample data...If you go into query design you will see all the additional lines access added (im still not sure why)

The query also does not filter the data correctly from the form.

Im totally confused on this one.

 

[plog]

Looks like its working to me. I'd ask for specific data where it fails, but the foundation of your database is flawed.

First, here's why this thing 'fails' in your mind--scroll to the very bottom of that horrible criteria list. Under the [Charge RC] column find the last input with '[Forms]![DetailQueryForm]![rcsearch]' in it.

Reason #1. Scroll all the way to the right of that line--there's no other criteria. So when you type in 7237 into the top drop down of your form, every single 7237 record will show, because on that line, its applying no other criteria. If you use any other drop down on the form, it doesn't matter because that line let's through all records for 7237.

Reason #2. Look at the 2nd to last line--the one right above the one I just described. There's no criteria on [RC Search], but if you scroll to the far right you will see there is criteria on drop downs 84 and 85, specifically Null criteria. So, if a user leaves drop downs 84 and 85 blank all results from your table will be returned. Who cares about any other drop down on the page, if 84 & 85 are not input, every record comes through.

That's why you think it 'fails'. Now here's the real fail--your table is not structured properly. When you start putting values into field names, you have the wrong structure. I have no idea what all those [HDQMGT...] fields are, but they should not be field names--they should be fields in a table. You don't have a field name for every person's last name, you have a field called [Last Name] where you input the values that can be used for last names. That's what you have to do with all those [HDQMGT...] fields. You should have some new table with a field called [HDQMGT] where you store the correct number in the field (e.g. 0084, 0085, ...) then another field caled something like [HDQMGT_Status] where you store the value you have in all those fields now (Pending, Completed, ...).

That would be the proper structure and with that, you get away from thsi monstorous query and can implement something easier to code and more proper.

 

[ria4life]

Thanks for the explanation here:
With regards the query.. Access added all those additional fields when i started testing out the form.

My initial query had only one criteria in each field Criteria ...Everything below the OR field was added automatically by access when i started using the form to filter data.
My initial query ended with Field [Rev 2016]...all the columns beyond that was added in my access.
Do you know why it's automatically added all those criteria?
Is it because of the volume of data im working with...(ie over 2000 line entries) ?


With regards the table:
the HDQMGT's are course numbers hence why i have them as columns....and im tracking whether an employee completes it or not via the three statuses (Pending, Completed, Not Required)

 

[plog]

Do you know why it's automatically added all those criteria?

That's just how Access does it. Kind of like unnecessary parenthesis. Access can really junk up SQL. With that said, it didn't change the logic of your criteria. If you look at the 2 code snippets in your initial post, they are logically equivalent. It changed the SQL, but not the effect of the SQL. I have faith that what you input into the Query designer is logically equivalent tot he SQL that Access generated.

Of course, that's moot. The real issue is your table structure. Your explanation of why you did it like you did only verifies that my analysis was correct. You should not have course names/numbers/etc in field names. You don't have columns for people's last names do you? No, you store last names as values in fields. you need to do the same with course names/numbers/etc. Very generally, your data should look like this:

ID_Employee, CourseNumber, CourseStatus
1, HDQMGT0084, Completed
1, HDQMGT0085, Pending
1, HDQMGT0086, Not Required
....

ID_Employee will be a foreign key (google that term) into a new table for all your employees. You don't keep relisting their first and last names over and over, you have a table that does that, then you assign them an ID and you use that ID in this table.

The other 2 fields (CourseNumber and CourseStatus) accomodate the data in the improper strucutre you currently have. It makes the course number a value in a table and not a name of a field. CourseStatus will hold the value you currently use in all those [HDQMGT...] fields.

Another term to google is 'database normalization'. That's the term used for the proper method of structuring your tables. Read up on it, find a few tutorials and then structure your tables according to its rules.

 

[ria4life]

Thanks Plog.

I will redesign the Db as you described and create necessary links to see how it works out.

thanks