Return Records with Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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.
 
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.
 
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
 
I had a feeling the data would get all messed up. Let me try again:

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


Entry Tshirts TShirtsPaid
1 2 $10.00


Entry Tshirts TShirtsPaid #NightsCamping CampingFee CampingSpace
2 1 $5.00 1 $25.00 A10


Entry #NightsCamping CampingFee CampingSpace
3 2 $50.00 B6
 
Normalize your data and return it in a report. You could possibly use a
multi-column subreport.
 
Back
Top