Using field values in more than 1 table/Form

[KevDBF]

I am driving myself mad with this!!

I am fairly new to Access and I am sure this is simple.

I am creating a db to dial outbound records to generate sales leads. I have 2 main tables
1) The customers
2) The available dates to book and the sales persons.

What I want to do is after getting a lead, go to a form that shows the available dates (whilst keeping a track of the Customer ID) and once a date is selected pop a confirmation form which shows

Customer ID (From table 1)
Date Selected (From table 2)
Sales Person (From table 2)

And once confirmed write all this info to the correct reocrd of table 1.

hope this all makes sense!

 

[GingerR]

Hi. Did you have a specific question? The best thing to do is to list table structure/field names and types, what you have tried so far, what isn't working for you, how you want it to work, sample data, etc.....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[KevDBF]

Thanks Ginger

Here is the structure of both tables:

Table 1
Fields Type Value
ID AutoNumber
Title Text Supplied Data
Surname Text Supplied Data
First name Text Supplied Data
Address 1 Text Supplied Data
Address 2 Text Supplied Data
Address 3 Text Supplied Data
Town Text Supplied Data
Post code Text Supplied Data
Tel Number Supplied Data
Media Code Text Supplied Data
Branch or Home Tick Box Needs to be written
Appointment Tick Box Needs to be written
Appoint AM/PM Tick Box Needs to be written
App Date Date Needs to be written
Dispenser Name Text Needs to be written
Dispenser Code Text Needs to be written

Table 2
Fields Type
ID Autonumber
Date Date Supplied Data of Available Apps
AM Tickbox
PM Tickbox
Branch? Tickbox
Dispenser Name Text Supplied Data
Dispenser Code Text Supplied Data
Quota Number Supplied Data

Table 2 is a table that's set up to display the dates per dispenser that are available. There could be 100 salespeople (dispenser) so that's 100 rows per date. I couldn't think of an easier way of doing this?

The idea is then that when you have secured an appointment, the link criteria is the Dispenser code - which looks up the free apps on the other table.

I need to be able to automate the input of the date selected back into Table 1.

Thanks

Kev

 

[lespaul]

First off, it's apparent that your database isn't normalized since you have Dispenser Name and Dispenser Code in the two tables. You also seem to be deciding how to store the data based on how you want to display the data; these are two separate processes.

Have you read the link below?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[GingerR]

I agree with Lespaul. Seems like you want to store the same data in two different places? Please supply some sample data

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html