Query howto using Access

  • Thread starter Thread starter vasilepop
  • Start date Start date
V

vasilepop

Hi all,

I have 2 tables USERS and BILLS with the following structure:
USERS:
ID - Primary key
NAME, SURNAME, ADDRESS, ...
and
BILLS
ID - Foreign Key
DATA_JAN, DATA_FEB, DATA_MAR .. DATA_DEC
In the second table there are 12 fields, for each month of the year an
in these fields there are inserted the dates when the users have paid.
I would like to perform a SELECT to get the NAME, SURNAME and the LAS
PAY.
LAST PAY should be the last day of the month from DATA_JAN ... DATA_DE
where DATA_* <> NULL.
How can I do that ? I supose I have to write a function which wil
perform that ? Can somebody help me please ?
Please note that I'm a newbie to VB + Access.

Thank you,
Vasil
 
Vasile, the structure is not right.

Instead of having 12 fields (one for each month) and presumably another 12
for the amount actually paid in each month, just have two fields:
PaidDate Date/Time when the payment was made
Amount Currency The amound of this payment.

This way:
- It is really easy to sum the payments;
- You don't have to worry about the blank records for future months: they
get entered when the date comes;
- The database continues to work correctly next year;
- You can easily filter on any date range to get the sum of payments in any
period, e.g. a quarter, a calendar year, or a financial year.
 
Yes, I know that, but that's how it works.
There are many aplications depending on that structure. The BILLS table
is changed each year.
I just have to get the right result (NAME, SURNAME, ADDRESS, the date
of the last payment) and I don't know how can I do that ...
 
Start at the last field and test IsNull(), working backwards until you get a
value.
 
Back
Top