Ap******@gmail.com wrote:
Hello
I'm having a problem creating a sql statement that works for me.
I have 1-Table.
I have 3-Fields in this table that are bound checkboxes (Boolean)
Apple/Cherry/Grape. Any of which may or may not be checked.
How do I generate a SQL statement that will do the following in one
report ?
See my comment afterward. It's very important:
I will assume that a record can only be one of the three flavours.
SELECT * FROM [TABLE1] WHERE [Apple] = -1 or [Cherry] = -1 or [Grape]
IN report design mode, choose View->Sorting and grouping.
In the first cell, type in the following expression which will give a
value of 1 to apple, 2 to cherry, 3 to grape. this is air code, but if
you copy and paste the exact text below into it, it should work:
=iif(Apple = -1, 1, iif(Cherry = -1, 2, 3)
(An alternate way of doing this would be to use the switch function.)
IN the sort order, choose ascending
Below, turn Group header On = Yes and close the sorting and grouping
dialog. You'll see a header on the report.
Put an unbound text box into it.
View the text box's properties and put the following into the
controlsource (this is similar to the expression above for sorting and
grouping) AIR CODE:
=iif(Apple = -1, "Apple", iif(Cherry = -1, "Cherry", "Grape")
Now for my comment.
If my assumption about only one flavour being applicable per record,
your structure is very bad and YOU SHOULD STOP RIGHT THIS MINUTE AND GO
NO FURTHER until you've fixed things up.
When you design a table, the table describes some sort of data entity,
such as a list of ice cream products, or a list of music cds you own.
Each entity has attributes:
for a music cd some common attributes might be:
1) Genre (country, rock, classical, etc)
2) Artist (Bethoven, Pink Floyd, etc)
3) Music company
For ice cream, things like "apple", "cherry", "grape" are ONE attribute,
ie, flavour.
IN database design, it is BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD
BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD
BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD
BAD BAD BAD BAD BAD practice to have separate fields for the same entity
such as you have done above.
Use one field and call it flavour. Not a check box field for each flavour.
If you understand what I've written, then yopu've made a big step
forward. There's more to it than this, of course, ie, each of the three
attributes listed for music cds could, and probably should, be listed in
separate tables from the cd list table.
Similarly, especially of you are going to have a lot of flavours that
could be added to later, you could set up a separate table for flavours,
give em an autonumber PK and store the PK with the main table to
indicate flavour.
If the number of flavours is not likely to change, in your table design
for the field "flavour" you can limit choices by doing the following:
Highlight the flavour field.
Look below and in the VAlidation Rule cell, type in the following:
=("Apple", "Cherry", "Grape")
Access will now only allow those values in the flavour field. There are
ways to make data entry easier, but you'll have to get involved with
making forms.
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
