Access help: Running queries with checkboxes

[bubbasmith99]

i am working on a customer service database where we log the type of call.

these 'call types' are not in a drop down list, rather, they are check boxes.

so, let's say these are the call types: red, blue, green, purple, black, white

as a result, in trying to run a crosstab query which will let me break down the frequency of call type by month over the coruse of the year, i'm unable to get a straight number but instead have to deal with row upon row of checkboxes.

so i dont get


jan feb march....
red
blue
green
purple
black
white

but instead i get a whole slew of gibberish.

does anybody have ANY clue what i'm talking about?

i need to run a query on a bunch of stinkin check boxes. someone help!

 

[EagleKeeper]

If the checkboxes are the trigger for the end of the data, just trap the event for each checkbox and run your query accordingly.
If another event triggers the end of data, then just have code that test the checkbox state and the do your query.

 

[nickaskew]

Hi

Ok, lets clarify what you have -->

From what you say, I believe you have an OPTION-GROUP field on your form which you use to select whether the call was 'Red','Green'','Blue' etc. If you click 'RED' and the option was on 'GREEN', green should get unchecked and red will have the check in it.

If this is correct then read on!

A FRAME control on a form allows the developer to give you a GROUP of options but only allows you to select 1 of them at any one time. For example, in my table I need to store the call type as a number (1=Red, 2=Green, 3=Blue, 4=Cyan etc) - by using an option-group field I can give the database user a way of seeing RED, GREEN, BLUE, CYAN so they can check only one and convert this to a number to store against the call record in calls table.

If you attempt to create queries based on the CALLTYPE field in the CALLS table all you are ever going to see is a numerical representation of the option your users chose.

Workaround:
1) Create a new table, call it 'tblCallTypeDescriptions',
2) the first field s(call it "calltypeid")hould be a numeric that matches the number format of the field in your CALLS table (it is probably a long integer)
3) the second field should be a text field (call it "CallTypeDescription" and leave the size to the default of 50)

4) Save and Close the new table

5) Click on the Microsoft Access TOOLS menu and select RELATIONSHIPS
6) Right click in the relationships window and select SHOW TABLE
7) Ensure both the CALLS and tblCallTypeDescriptions tables are visible.
8) Drag the field you created in the tblCallTypeDescriptions ("calltypeid") onto the Call Type field in your original CALLS table - a little dialog box will pop up asking about the relationship you wish to setup - click the CREATE button

Now, in the new table you created, enter some data, the first record should be like: calltypeid=1, CallTypeDescription=Red, the second record like: calltypeid=2, CallTypeDescription=Green, etc.
Whenever you create a query based on your CALLS table and you wish to use the CALL TYPE field, use the CallTypeDescription field from your tblCallTypeDescriptions instead (just make sure the table is visible in Query Design View - or choose this table during the Query Wizard) - you will notice that whenever you make a reference to both your original CALLS table and tblCallTypeDescriptions that a link is visible between the two tables.

Yeah, having re-read this it isn't easy to follow, if you have trouble email me.. it is visible on my profile, just click the icon below my name on your left..

Regards

Nick