Adding record in table not correctly opened

[itsmystie]

The system is about a shop which gives credit facilities to its customers and so has to keep track of payments effected by them.

I have 3 tables: Customer, Payment, Balance. The main table is Customer, with primary key CustCode which is an autonumber. It keeps personal details of the customers. The Payment table keeps details of payments effected by the customers and the Balance table keeps the overall balance for each customer. The relationships are as follows:

Customer to Payment, 1-to-many
Customer to Balance, 1-to-1

Fields in Payment table: CustCode, PaymentDate, AmtPaid
Fields in Balance table: CustCode, Balance

Problem 1
I have a data entry form for Customer. When I create a new customer record, I want a new record automatically created as well for the customer in the Balance table. How can I do that?

Problem 2
I need to produce a report on customers who have not made any payments during the previous month. I’d like to know how to do that as well.

I hope I’ve made myself clear enough. Thank you for all help.

 

[RuralGuy]

I think you should review your design. I believe the balance should be a sum of the "Activity" table which should contain both debits and credits. Then Problem1 is just an entry in the Activity table.

 

[itsmystie]

Thanks RuralGuy, but still the question is the same. How do i add a record for the customer in the Activity table when i create a new customer record?

 

[Rich]

Use a subForm

 

[itsmystie]

again thanks 4 the answer, but i'm still confused. i'm relatively new to Access. i've put a subform but i still cant see how i can create the new record automatically. i don't want the user to have to click the New Record button in the Balance subform every time there's a new customer. i want it to be done automatically.

and nobody said anything on how to do the report on customers who have not made any payments during the previous month.

 

[NNothard]

Re

Create a subform using the toolbox and the wizard will run you through the process, you will need to specify the link between the two forms, ie Client_ID (this will be the same link you see in the relationships window)


For the report, I suggest you create a query based on the info you want in the report. For the field where you are looking for no value enter 'Is Null' into the criteria section for that field.

Good Luck