2 similar queries acting different....

[CQCDave]

I have 2 queries that are almost identical one works fine...the 2nd one requests a parameter entered. I'm using MS Access 2010.
#1
SELECT DISTINCTROW [CustomerInformation].CustomerID FROM CustomerInformation WHERE ((([CustomerInformation].CustomerName) Like forms![Customer Complaints]!BSICustomerName));

This queries works correctly.



#2
SELECT DISTINCTROW [MedicalOptions].HealthCareCost FROM MedicalOptions WHERE ((([EmployeeTable].HealthCareCost) Like forms![ MedicalOptions]!HealthCareChoice));

This one will prompt for Forms! MedicalOptions!HealthCareChoice


Any help would be appreciated.

Thanks!

[JoeM]

If you get the prompt, it usually means that either:
- the Form is not open when you run this query (it needs to be)
- You have a typo (in your Form or Field name)

[CQCDave]

Thanks, i went back and checked table/field names and all were correct. I have went another route and am getting some results just now that results that i should get. This is the query as i'm using now;

SELECT MedicalOptions.HealthCareCost FROM EmployeeTable INNER JOIN MedicalOptions ON EmployeeTable.HealthCareChoice = MedicalOptions.HealthCareOption WHERE EmployeeTable.HealthCareChoice Like MedicalOptions.HealthCareOption And MedicalOptions.HealthCareOption Like EmployeeTable.HealthCareChoice;

When the results return i get 3 different values when i should get only 1.

The values in MedicalOptions table are;

HealthCareOption	HealthCareCost
Employee Only RM26 Plan	$36.75
Employee + Spouse RM26 Plan	$80.21
Employee + Children RM26 Plan	$69.22
Employee + Family RM26 Plan	$112.68
Employee Only Buy Up RM25	$44.57
Employee + Spouse Buy Up RM25	$97.27
Employee + Children Buy Up RM25	$83.96
Employee + Family Buy Up RM25	$136.65

In the EmployeeTable i have selected Employee Only RM26 Plan which should return a value of $36.75, it however returns a value of $36.75, $80.21, and $83.96.

[JoeM]

When you get more results than you expect, often times it is an issue with how you are joining.
My guess is you are missing a level of joining, and have a one-to-many relationship happening instead of a one-to-one relationship. It sounds like you are successfully joining on your "RM26 Plan" fields, but missing a join or criteria to limit it to the single coverage level you desire.

Without seeing the structure of your tables and some sample data, it is hard to be any more exact than that.

[CQCDave]

Here is a copy of the database. The query is attached to the HealthCareCost field in the Employee Form.

https://app.box.com/s/u84n8vegtck2itii6y4c8nve8ktqiloq

Thanks...

[JoeM]

You can actually attach databases right to your replies here, if you use the Advanced reply tab.

If you attach it here, I will download it and take a look at it tonight. I am unable to download files from my current location.

[CQCDave]

Here you go, thanks for your help.

[klrich]

So you are looking at where HealthcareCost and HealthcareChoice are the same?

SELECT DISTINCTROW [MedicalOptions].HealthCareCost FROM MedicalOptions WHERE ((([EmployeeTable].HealthCareCost) Like forms![ MedicalOptions]!HealthCareChoice));

[CQCDave]

I did see and change that to;
SELECT DISTINCTROW [MedicalOptions].HealthCareCost FROM MedicalOptions WHERE ((([Employee Table].HealthCareChoice) Like forms![MedicalOptions]!HealthCareOptions));

and was still getting the same results.

[JoeM]

The issue is your WHERE clause does nothing. Instead of having criteria entered through a Form, through a Parameter, or hard-coded, you set two table field as the parameter (fields from the tables you are joining on). It doesn't do anything, because it is selecting all the values in those fields. So it is essentially doing nothing. If you remove the entire WHERE clause from that SQL code, you will see that your results are exactly the same (3 records returned).

Can you explain exactly what you are trying to do with this query, how you want it all to interact?

[CQCDave]

From the Employee form the user selects Their HealthCareChoice from one of the 9 choices. Once that is selected they click on the HealthCareCost, the only return from the query should be the cost associated with their HealthCareChoice. If the choose Employee + Family RM26 Plan the only value that should be returned from the query would be $112.68. Thanks for your help.

The values in MedicalOptions table are;

HealthCareOption	HealthCareCost
Employee Only RM26 Plan	$36.75
Employee + Spouse RM26 Plan	$80.21
Employee + Children RM26 Plan	$69.22
Employee + Family RM26 Plan	$112.68
Employee Only Buy Up RM25	$44.57
Employee + Spouse Buy Up RM25	$97.27
Employee + Children Buy Up RM25	$83.96
Employee + Family Buy Up RM25	$136.65

[JoeM]

I really don't follow the flow of your database. I am thinking that maybe you have stripped some objects out for a scaled down version you have posted. You only have one Form in this (and no queries).
Do you have other queries and forms that you have not included?

What is the purpose of the one Form you have included? There are data entry forms, record selections forms, etc.
Also, how does the query you posted up in step #3 come into play with this year?
If you are just trying to make a record selection form where you enter parameters and return results, it should probably be an unbound form (your form is a bound form).

[CQCDave]

i'm building this db to aid the HR department with the Employee Benefits offered at the company. I'm just beginning the process. The query that i'm working on now is what is stopping the rest of the project. Once i get this fixed and i can continue.

[JoeM]

I think we are going to need a lot more details to really help you out. A good start is to look at and answer all of the specific of the questions I asked in the previous post. It appears to me that you might be mixing up/confusing different kinds of forms (or trying to do too much in a single form - trying to use it for two many different purposes).

Let's focus on the singular task at hand. Explain your goal, plan, and exact methodology you are trying to use to get what you want. Be specific (something like "I am trying use Query X and/or Form to do such and such. I want the user to enter Y in field Z and return all records that meet that in a query").

Also, how experienced are you with Relational Database Design, Rules of Normalization, and Microsoft Access? In order to design a really good databases, it is really important to understand all those concepts. You'll drive yourself crazy if you are not familiar with all those concepts and just try to "wing it". I speak from experience, because that is how I tried designing my first real Access database.

[CQCDave]

The purpose is for the HR administrator to have a form to enter and display each employee's choices and associated costs with the heath care benefits offered at the company. The administrator will update each employees record in the employee table through the bound employee form. In the Employee form the admin will select the employee's choice on health care plan from the HealthCareChoice plan field. This is a combo box the queries the MedicalOptions table. After they have selected that field they will click the drop down list in the HealthCareCost field. A query will search the MedicalOptions table match the selection of the HealthCareChoice from the field in the Employee Table with the HealthCareOption in the MedicalOption table. That cost associated with the HealthCareOption should be the only response returned.

I am pretty new to Microsoft Access. I have taken a couple of classes. Through ONLC training centers. I have taken the beginners and advanced classes. They did give me some tools to use in development. Not a whole lot on the relational database design. I do have some development background,but in a different software. I previously developed in Remedy Action Request Systems environment, but that was a few years ago.