update field with specific content

[luxeon]

hi... first sorry for my English...
So, i have in access 2 tables:
1. information about customers (name, address, debt (month));


2. information about acquittals (year, id_custom (for making relation with firs table), January, February etc. (months are of type - yes/no)) if customer is acquitted then check the box

now i need to update the field [debt (month)] from first table with current information, how many boxes are unchecked since last payment until now (current month and year) thx very much...

[jzwp11]

Welcome to the forum!

In your acquittals/payments table, are you saying that you have the following fields?

acquittals
-year
-id_custom
-January
-February
-March
etc.

If so, then your table structure is incorrect.

I would just record the actual date of the acquittals/payments in the table

tblAcquittals
-pkAcquittalID primary key, autonumber
-fkCustomerID foreign key to tblCustomer (same as your Custom_ID)
-dteAcquittal (the date of the payment)

I'm not sure, but shouldn't you record the amount of the acquittal/payment? If so, that would go in tblAcquittals as well.

In your customer table, you would not need the field debt (month) since you can determine the time between the most recent payment and the current date using a query.

The way I have structured the table above is based on the rules of database normalization. For more information on normalization, please check out this site.

[TheShabz]

I would set up your DB separately. I would have 3 tables
tblCustomers (set up as you have it)
tblMonths (one field containing all the months of the year)
tblAquittals (AquitallID (PK, autonumber), id_custom, month)

Joins are:
tblCustomers 1---M tblAquittals M----1 tblMonths

the tblAquittals would now have separate entries for each customer and each month. you then check for count(custID) WHERE month = 0

__________

Edit: disregard this. Had the window open for awhile and didnt see jzwp11's reply. His is better as far as the actual application goes.