Newbie - Query Advice Please

[DNDPMick]

Morning All,

Yes me again. Sorry to be on here regularly asking for help. My social enterprise uses a access database to capture customer detail for one of our customers. We have one table which holds all the account information and one table which allows us to log calls per customer. I think it was originally a template from the MS website.

The system is working well for us now, but I would like to make a change which would make the database even more usefull for us.

Some background:

The customer carry out work for is a laundry. Their customers call us to book pickups and deliveries of laundry items. To log a call, the operator finds the customer contact record using access search function, then selects the calls tab (page) of that record. The calls tab automatically has todays date in it for the call date, the operator changes the type of call ie Booking, Enquiry, Complaint, Other. In the case of a booking, tomorrows date is automatically entered into the booking date, and a return date is automatically generated for 7 days later. There is a notes field which is offered for any notes or special instructions.

In the case of a lot of these customers, they have standing special instructions for their pickups and deliveries, which are captured on the account page. I would like to have any notes that are on the account page, automatically copy over to the calls table when a call is logged. I have tried to do this, however when I run the query it shows every record that has a special instruction. Regardless of whether they have booked a visit or not.

Okay that was long winded. basically when a call is logged against a specific customer, I would like the contents of a field in the contacts table copied over to a field in the calls table, but only when a call is logged.

Hopefully this explanation makes sense.



Any other info you need please just let me know & thanks in advance for any advice of help you can give.

Mick

[orange]

I recommend you work through this tutorial from Rogers Access Library. You have a good description of WHAT you want/need. The tutorial will help you identify the tables and relationships to do it. Create the model/diagram and use some test data to ensure your model is correct. Then, adjust your database accordingly.
Always work on a copy of the database --don't start modifying your production database.

For more info-- watch the Database series by DR. Daniel Soper on youtube starting here.

Good luck.

[DNDPMick]

Thanks Orange, ill take a look and see what I can worth through. Really appreciate the pointer.

[June7]

Should not duplicate data between tables, just save keys that link related record. The Rogers' tutorial should help you get a better understanding of relational database principles.

In other words, the special instructions would not be copied into the calls table, only the customer ID should be saved and then in a query join tables by linking on the key fields so the associated instructions can be viewed.