Return only fields with values in a query

[jdks2006]

I have created a receipt table that gives one receipt number for each check received. On this table a customer can pay up to 12 invoices with one check. My problem I have ran into is, when I run a query I only want the fields (invoice #'s) with values (paid amount) in them to return. I don't need to see all 12 fields (Invoice #'s & their amounts) if the customer only paid for say 5.

I know there is a button to "show" checkbox for the fields. By not having the fields show, I could miss an invoice that a customer has paid for and obviously I don't want that.

[Rabbit]

You should have a separate invoice table with one record per invoice.

[mcupito]

In your query, for the Criteria or WHERE, "[PaidAmount] Is Not Null".

I know some people are against the use of "Null" however I think you could also put "[PaidAmount] > 0" also, going on the assumption people can't pay with $0.00

So, if your query currently brings back ALL records (invoices and paidamount), adding the Criteria on the PaidAmount should filter the records.

You could also use this to filter whether or not some one has NOT paid, by reversing the logic.
[PaidAmount] Is Null Or [PaidAmount] = 0

If you build your queries in Query Design, become familiar with the Criteria option.

Hope this helps.

[NeoPa]

I'm afraid you cannot filter records that way and expect the results you want. If a record is included or not will affect all fields in the record equally. IE. You filter out records not fields.

This gets us back to Rabbit's comment. This may seem a little short and possibly unhelpful, but therein lies everything you need to help you get to a workable solution.

Your table design is not Normalised. Many people have discovered that databases designed that way always end up reaching a point where they become unusable for what's required. It's important to learn from others' experiences, so here's a link that will help (Database Normalisation and Table Structures).

Trying to continue with your current design will only lead to more and more things you may want to do but will be unable to, as in this case.

[mcupito]

@NeoPa, I overlooked his request of 'Fields.' Thanks for the correction.