1)How to search for blank fields? 2)How to track Payments ??

  • Thread starter Thread starter MyEmailList
  • Start date Start date
M

MyEmailList

Two completely seperate questions...

1 - Have a table with bunch of fields & ten of those fields may be
blank. How can I search multiple fields at once using a query... say
fields M through X... and see if any of those fields... in any of
40,000 records... have data in them?

2 - I have a tblAccounts, tblOrders and tblOrderItems in my db. The
orders may be delivered over several months and the customers may pay
all up front, in 2 or 3 equal payments at monthly intervals, or pay so
much per month over the course of delivery.

How do you design the table structure for payments? Do you have a
tblPayments as a child of tblOrders or of tblOrderItems... or what?

thanks for any help.

Mel
 
Two completely seperate questions...

1 - Have a table with bunch of fields & ten of those fields may be
blank. How can I search multiple fields at once using a query... say
fields M through X... and see if any of those fields... in any of
40,000 records... have data in them?
Click to expand...

Use a criterion in your query of

IS NULL

to find records where a field is blank; IS NOT NULL will find records where
the field is *not* blank. If you have multiple fields and want to find a
record if *any* of them contain data, put

IS NOT NULL

on the Criteria line in the query grid under each of the fields, on separate
rows so that it uses OR logic.

If you have fields named M through X containing the same logical category of
data, I suspect that your table is incorrectly designed! It looks like you're
"committing spreadsheet", storing a one-to-many relationship in each record.
2 - I have a tblAccounts, tblOrders and tblOrderItems in my db. The
orders may be delivered over several months and the customers may pay
all up front, in 2 or 3 equal payments at monthly intervals, or pay so
much per month over the course of delivery.

How do you design the table structure for payments? Do you have a
tblPayments as a child of tblOrders or of tblOrderItems... or what?
Click to expand...

Depends on your business rules. Logically, does a payment apply to an Order?
or does it apply to an individual OrderItem? If a customer orders five items,
do you expect five payments, one for each item; or do payments get applied to
the order as a whole? The latter would seem to be much more common; if that's
what you want, then the Payments table would be related one-to-many to the
Orders table.

John W. Vinson [MVP]
 
Back
Top