Access question

  • 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
 
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 and
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 LAST
PAY.
LAST PAY should be the last day of the month from DATA_JAN ... DATA_DEC
where DATA_* <> NULL.
How can I do that ? I supose I have to write a function which will
perform that ? Can somebody help me please ?
Please note that I'm a newbie to VB + Access.

Thank you,
Vasile
Click to expand...

Hi Vasile,

This is going to be difficult with the table structure you have. The
structure of your Bills table is not normalized. Tables should not have
repeating fields like Jan, Feb, Mar, etc.

Instead, the Bills table should be something like:
ID
BillDate

Every Bill for every User will be a separate record in this table. Then
your query becomes easy. You can use a totals query to find the Max
BillDate, grouping on ID, Name etc.

By the way, this normalized structure also allows for multiple years,
multiple bills per month, etc.

Hope this helps,
 
Back
Top