Re: Return Records with Values

Tech-Archive recommends: Fix windows errors by optimizing your registry

Ok, here is a sample of the data:

Entry TShirts TShirtsPaid #NightsCamping CampingFee
CampingSpace
1 2 $10.00 0
$0.00
2 1
$25.00 A10
3 1 $5.00 2
$50.00 B6


Here is a sample of the desired output:

For Entry=1
TShirts TShirtsPaid
2 $10.00

For Entry=2
#NightsCamping CampingFee CampingSpace
1 $25.00 A10

For Entry=3
TShirts TShirtsPaid #NightsCamping CampingFee CampingSpace
1 $5.00 2
$50.00 B6

Does this help? For whichever entry I pick, I only want to return what they
did. If it is not possible, thenI suppose I have no choice but to return all
rows for each entry.
Thanks for trying,
DN

"John Spencer" wrote:

DN,
If I understand your question.

You cannot do what you want in a query. A query returns a uniform structure
of columns and rows.
You cannot have
-- row 1 with fields A, B, and C in positions 1, 2, and 3
-- row 2 with fields B and C in positions 1 and 2
-- row 3 with fields A, C, D, F in positions 1, 2, 3, and 4.

With some VBA code you could do something like what you want in a report.
This would involve a query returning all the fields and then either setting
the values of numerous control in the report or showing/hiding relevant
controls and moving the controls around for each detail line of the report.


"Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxx> wrote in message
news:ebYjGd2NGHA.2128@xxxxxxxxxxxxxxxxxxxxxxx
Your description doesn't clear up much since you didn't provide any sample
records or desired output.

One thing your field names suggest is an un-normalized table structure. I
would suggest a table structure similar to the Northwind.mdb sample where
T-shirts, camping nights,... are products listed in the order details.

If you can't change your table structure, you might consider a union query
to filter out non-purchased items.

--
Duane Hookom
MS Access MVP
--

"DN" <DN@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:25F8BA16-E6C8-443B-A977-0038C6779D3F@xxxxxxxxxxxxxxxx
You are right. I can see where it might not make much sense.

Some of the fields for each "entry" in my table are:

#TShirts - number
TShirtsPaid - currency
#NightsCamping -number
CampingFee - currency
CampingSpace - Text

When selecting a particular "entry" I want to return only the fields with
data. I want to see the fields with data for "Entry X". If "Entry X"
didn't
puchase any tshirts, but did spend 1 night camping for $25.00 in space
10, I
want to see that and not anything about tshirts.

Does that help?
Thanks for your assistance.
DN



"Duane Hookom" wrote:

This question doesn't make much sense. It would help if you provided a
few
sample records and desired output.

With queries, you can choose not to display entire records or not to
display
full columns/fields.

--
Duane Hookom
MS Access MVP
--

"DN" <DN@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:90C037E5-541D-4476-98B3-4023DCE9E7C8@xxxxxxxxxxxxxxxx
I have a table that has many fields of data. The fields are text,
currency,
numbers and memo values. How would I structure a query to return only
those
fields that have data? Some fields may be blank, some will have a
default
value of 0, others may have $0.00 currency default values. I am
trying to
compile a report that only returns the fields with data.

Any help would be appreciated.
DN








.

Relevant Pages