By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,739 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,739 IT Pros & Developers. It's quick & easy.

SQL for Report

P: n/a
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 ?
Print Apple Heading
Repeat
Print EachRecord If Apple = True '
Until Done
Print Cherry Heading
Repeat
Print EachRecord If Cherry = True '
Until Done
Print Grape Heading
Repeat
Print EachRecord If Grape = True '
Until Done

My attempt below does not return what I am looking for in the proper
order.
The results become mixed and has much to do with the way UNION ALL
works and my newness with SQL:

SELECT * FROM [TABLE1] WHERE [Apple]
UNION ALL
SELECT * FROM [TABLE1] WHERE [Cherry]
UNION ALL
SELECT * FROM [TABLE1] WHERE [GRAPE]

Apr 25 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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
Apr 25 '06 #2

P: n/a
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 ?
Print Apple Heading
Repeat
Print EachRecord If Apple = True '
Until Done
Print Cherry Heading
Repeat
Print EachRecord If Cherry = True '
Until Done
Print Grape Heading
Repeat
Print EachRecord If Grape = True '
Until Done

My attempt below does not return what I am looking for in the proper
order.
The results become mixed and has much to do with the way UNION ALL
works and my newness with SQL:

SELECT * FROM [TABLE1] WHERE [Apple]
UNION ALL
SELECT * FROM [TABLE1] WHERE [Cherry]
UNION ALL
SELECT * FROM [TABLE1] WHERE [GRAPE]


Here's an alternate method. Because the same record can fall into more
than one category an IIF filter is possibly too restrictive. Maybe use
three subreports that each Filter a fruit, e.g., [GRAPE] = -1, with the
FilterOn = True. Totals from the three subreports can be obtained, if
desired, by placing three hidden textboxes on the main report for each
subreport total that needs to be totalled. The ControlSource for the
report footer's visible totals can be something like:

=Format(IIf(Reports("rptMain").[Controls]("rptSubGrape").[Report].[HasData],Nz([txtSubtotalGrape].[Value]),0)
+ ...,"$#,##0.00")

where txtSubtotalGrape is the first hidden textbox on rptMain. It's
ControlSource looks like:

=[rptSubGrape].[Report]![txtSumGrape]

from the subreport's Report Footer.

You don't have to use the same table for each subreport so you can, in
effect, add apples and oranges :-) :-). Also note that since some
records can be contained in multiple subforms you need to be careful
about what the totals of all three subforms mean. By all means try for
something that doesn't require subreports first.

James A. Fortune
CD********@FortuneJames.com

Apr 25 '06 #3

P: n/a
ThankYou for the response.

I have 1-Table.
As mentioned:
I have 3-Fields in this table that are bound checkboxes (Boolean)
Apple/Cherry/Grape. Any of which may or may not be checked.

Note: Anyone of the 3 fields are allowed to be checked. All 3or2 at
one time or just one!

"BAD practice to have separate fields for the same entity "
I am very aware of the mentioned warning! And AMNOT doing this here.

I DoNot want to use subforms. SQL must allow this, it seems that this
could be a
frequently used thing.

ThanksAgain

Apr 25 '06 #4

P: n/a
"Ap******@gmail.com" <Ap******@gmail.com> wrote in
news:11**********************@y43g2000cwc.googlegr oups.com:
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 ?
Print Apple Heading
Repeat
Print EachRecord If Apple = True '
Until Done
Print Cherry Heading
Repeat
Print EachRecord If Cherry = True '
Until Done
Print Grape Heading
Repeat
Print EachRecord If Grape = True '
Until Done

My attempt below does not return what I am looking for in the
proper order.
The results become mixed and has much to do with the way UNION
ALL works and my newness with SQL:

SELECT * FROM [TABLE1] WHERE [Apple]
UNION ALL
SELECT * FROM [TABLE1] WHERE [Cherry]
UNION ALL
SELECT * FROM [TABLE1] WHERE [GRAPE]


You will have to set your sorting and grouping orders in the
report, and not rely on the query keeping your desired order.

--
Bob Quintal

PA is y I've altered my email address.
Apr 25 '06 #5

P: n/a
Ap******@gmail.com wrote:
ThankYou for the response.

I have 1-Table.
As mentioned:
I have 3-Fields in this table that are bound checkboxes (Boolean)
Apple/Cherry/Grape. Any of which may or may not be checked.

Note: Anyone of the 3 fields are allowed to be checked. All 3or2 at
one time or just one!

"BAD practice to have separate fields for the same entity "
I am very aware of the mentioned warning! And AMNOT doing this here.

I DoNot want to use subforms. SQL must allow this, it seems that this
could be a
frequently used thing.

ThanksAgain


Using tblFruits instead of TABLE1 and sorting and grouping on Title,

rptFruit:

Title Header

Title

Detail

FruitName Apple Cherry Grape

RecordSource: qryFruit

qryFruit:

SELECT 1 AS ForOrder, 'Apple Heading' AS Title, * FROM tblFruits WHERE
[Apple] UNION ALL SELECT 2 AS ForOrder, 'Cherry Heading' AS Title, *
FROM tblFruits WHERE [Cherry] UNION ALL SELECT 3 AS ForOrder, 'Grape
Heading' AS Title, * FROM tblFruits WHERE [Cherry] ORDER BY ForOrder,
FruitID;

Note: FruitName was a field I added to tblFruits. I did this in a
hurry but it works for the example I tried.

James A. Fortune
CD********@FortuneJames.com

Apr 25 '06 #6

P: n/a
ThankYou all for your advice. The following code became the
solution to my problem. Just substitute the fruit stuff!

SELECT "REPAIR" As MyFix, * FROM [T-SERVICE] WHERE [RPR]
UNION ALL
SELECT "MAINTENANCE" As MyFix, * FROM [T-SERVICE] WHERE [MAINT]
UNION ALL
SELECT "CALIBRATE" As MyFix, * FROM [T-SERVICE] WHERE [CAL]
UNION ALL
SELECT "BATTERY" As MyFix, * FROM [T-SERVICE] WHERE [BAT]
This code is all placed in the Record Source of the Report.
The REPAIR label becomes placed in MyFix when RPR = True, Maint, etc...

The sorting and grouping dialog is to contain the MyFix label, and
select the sort you want next to it. Set the group header to Yes. Then
place a
TextBox in the MyFix group header which appears in the report and set
its Control Source to MyFix.

Apr 26 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.