Query Help

[loo8866]

Hi there -

Having a bit of bother with a query - I have a table which holds a list of people. The table contains a few fields which are categories the people can be - eg member, help, volunteer etc, and these are yes/no fields. A person can be any number of these.

Now, i am making a form which will allow the user to type a letter, tick a category and a report will be run which is a letter for each person of that category. Eg the user may want to write a letter to all members - so they will select member from the combo box.

Im having problem with the query - i need it to look at which category is selected on the form and filter all people which have that category as yes...

Cheers for any suggestions!

 

[KeithG]

Can you post you SQL or an example db?

 

[rainman89]

is this a check box or a combo box?

 

[loo8866]

On the form it is a combo, where the user can select a single category - i want the query to show every person which has this category set as true. Ive got it to work filtering a datasheet with the code below, but having trouble doing it in a query:

Dim SearchCriteria As String

Me.Type.SetFocus

SearchType = Me.Type.Text

Me.subfrmContacts.Form.Filter = SearchType & " = true"

Me.subfrmContacts.Form.FilterOn = True

Me.Type.Value = ""

This code filters the datasheet to show the people with the selected category set as yes.

 

[rainman89]

I think, but am probably wrong that it goes something like

SELECT * FROM youtablename WHERE (Me.comboboxname & "= true")

 

[loo8866]

mm doesn't seem to work - I tried this -

SELECT *
FROM contacts
WHERE (([Forms]![frmContactLetter]![TypeC]) & "= true");

but no matter what i select, it just shows everyone!

 

[Simon_MT]

What are the fields called on the contacts?

 

[KeithG]

Is the True/False field of a boolean data type or text data type. I would assume boolean but from the above example looks like text.

Try the below if the data type is boolean.

SELECT * FROM youtablename WHERE (Me.comboboxname & "=" & -1)

 

[loo8866]

yeh its boolean - il give that a bash -

 

[Rich]

How many Yes/No fields are there?

 

[loo8866]

5/6? I previously had two fields which were called contact type - and the user would be able to select 2 types. But actually, a person could come under more than 2 types - so i changed to this way.

It works great, but just trying to get this query changed isnt working...

 

[Rich]

Then wouldn't it be better to have a one to many relationship between a member and contact type?

 

[loo8866]

u mean make a new table for contact types? Will this make anything easier - or just good practice?

 

[rainman89]

if multiple people have the same contact type and more than one contact type it would be good to have a contact type table and probably a link table if they can be more than one type of contact

 

[loo8866]

Sorry, still having no luck with this query.

Basically, id usually do a query like

Blah Blah Where FieldName = Variable.

THis is fine - but now im trying to do & gettin problems is:

blah blah Where Variable (which equals a fieldname) = True.

Can any1 else help?

Many thanks.

 

[Simon_MT]

I do understand your logiic - you do have to "flatten" the Table. It may not be normalised but it is cunning although slightly devious to Normalised people.

We have 6 Categories of Contacts

I would not have CombiBoxes just emulate your Table on the Form. In essence you have rigid Contact Types on your Table so there is no need to give users Options via combiBoxes.

I would put 6 TickBoxes with the Type of Contact in your Table.

Then just marry up the True values with OR in the ReportCall or Query.

Simon

 

[loo8866]

OK - so i change the combo box on the form to tick boxes of the types (as in the table).

How do i link this with the query tho?

 

[loo8866]

OK - strugling with this.

Basically the contacts table (full of people) has a number of types which can be either yes or no.

A form has matching tick boxes for all types - when the button is selected a query is run. I want the query to just show the people that include a type from the types ticked in the form.

Eg. the user may tick the 'member' and 'helper' tick boxes on the form. The query should only show people which have the member type AND/OR the helper type ticked in the table.

Its the and/or bit thats the trouble, as there is 6 types and a person may be any number of types.