Display Record based on value of combination of 2 fields

[NoviceAccessUser-Melanie]

In the detail section of my report that is based on a query, I would like to
be able to prevent the data from being displayed if the values of a couple of
fields fall in a certain criteria. I have an Deal, IND field, and a Num1 ,
Num2 field. etc. The report is sorted by Deal and IND.

If IND="Actual" and Num1=" " or null, when IND = "Original", the value for
Num1 should not print . So it means, if there are no Actual numbers stored in
the database, do not display the Original numbers even if they have already
been input.

Is this even possible? Please shed some light if there's a way I can do this
with a very elaborate iif statement. Thanks.

 

[Jeff Boyce]

If you have "repeating fields" in your table (e.g., [Num1], [Num2], etc),
you have a spreadsheet, not a relational database.

"How" depends on "what", and it sounds like you and Access could be working
overtime to compensate for a data structure that would fit better in a
spreadsheet.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"NoviceAccessUser-Melanie"

 

[Bob Barrows]

In the detail section of my report that is based on a query, I would
like to be able to prevent the data from being displayed if the
values of a couple of fields fall in a certain criteria. I have an
Deal, IND field, and a Num1 , Num2 field. etc. The report is sorted
by Deal and IND.

If IND="Actual" and Num1=" " or null,

A Number field can never contain a string ... Is this really a Number field?
or is it a Text field that contains numbers?

when IND = "Original", the

Huh? IND can't contain both "Actual" and "Original" at the same time can it?

value for Num1 should not print .

Err ... if it's empty it won't "print" anyways, will it?

So it means, if there are no Actual
numbers stored in the database, do not display the Original numbers
even if they have already been input.

Is this even possible? Please shed some light if there's a way I can
do this with a very elaborate iif statement. Thanks.

I concur with Jeff about your design, but your description is very
confusing. Try showing us a few rows of sample data - just enough rows and
fields to illustrate the problem. Then show us the resulting rows you wish a
query to display.

 

[NoviceAccessUser-Melanie]

I agree, this took me a long time to figure out a way to get my data to print
on a report. After a few select queries, then crosstab queries I was able to
flip the data around and get it to a report. Now the group I support do not
want to see part of the data if 'Actuals' have not been received, so I have
to manipulate the report to hide the data.
Anyhow, in the final query which I use as my data source, the report goes
through all the data and sorts by IND first. It displays all the 'Actual' in
line 1. Num1 is a number because I calc totals at the end.
The query looks like this:
Deals Ind IndSort Year1 Year2 Year3 Year4
Deal1 Actual 1 1234 5678
Deal1 Original 2 4657 6899 7880
Deal2 Actual 1 2233
Deal2 Original 2 1111 3445

Here's how the data would look like:
Deal 1
Year1 Year2 Year3
Actual 1234 5678
Original 4657 6899 7880

So on the report, the Original amount $7880 should not print on the report
bec there are no corresponding Actuals for Year3.

I know it looks like a spreadsheet but they wanted to be able to store the
data and no retype every month. I was able to get them to this point but
maybe it's too complicated to manipulate data some more and be better off
dumping report to Excel and they can pretty it up the way they want it.

 

[Bob Barrows]

This can get very messy, very quickly (actually, it sounds as if it
already is ... )

The idea is to get the actuals and originals for each deal into the same
record, allowing you to compare the yearx actual value to the yearx
original value. That will require two subqueries to be joined by the
Deals field. Then, that query will need to be the source for a union
query to get the actuals and originals back into their own rows.

Yes, very complicated. I don't have quite enough information. Will there
always be both an Actual and an Original row for each Deal? If not,
which will be the missing row?