Familiar question for everyone

[JohnD]

Been a year since being on this forum and its been a year since using Access. That being said - I dont remember a thing, especially when I hardly knew anything to begin with.

I am attempting to build a simple database that can track prospective and sold customers. Attached is a picture displaying my tables and the appropriate relationships (hopefully).

The trouble I am running into is designing the Forms that allow the data to all connect and be modified by a salesman. Obviously I want the correct customers to fall under the correct salesman and so forth. My original thought was to have a main form with the customers data and use the tab control and subforms to switch between the data. Im just stuck on implementing the Salesman into all of this and keeping the customers information correlated with the salesman and so forth.

Suggestions on this would be more then appreciated!!

John D

 

[Mike375]

A couple of questions.

Firstly, the three tables for Trim, Make and Body....are those for what applies to cars that have been sold or are they lists of available Makes, Trim and Body types.

Secondly, in "non access" terms where is the data base falling short for you. In other words what are things that you can't do or are hard/messy to do.

Do you have a situation where you are half Access and half bits of paper, cards and diaries etc.? Does the data base offer you the flexibility if a salesman is changed halfway through the deal or the car is changed or the deal is a cancel just before completion etc.

 

[JohnD]

Trim, Body, and Make (ford, chevy, dodge...)are lists of whats available yes.

A break down of this DB is to achieve one thing - organization. I do use a sales planner (old pencil and paper way) to track prospective customers that have come onto the car lot, but its messy and costly. 2nd, I need something to store my sold customers in and im using an Excel spreadsheet and it simply has too many columns and its not easy to read and so forth.

So, the DB is to organize and help me with follow up. My plan was to have reminders to call and or send out letters based off the time they come to my car lot & when they purchase a vehicle.

I suppose my struggle at this point is very simple and I am sure the answer is simple. I have a form as of right now with the customers information (works perfectly). Now lets say I want to add a record (customer) to the DB but I want to show that I am the salesman, not someone else. I have tried adding a list box with the names of the Salesman at the lot, but it will not let me add any information into the other fields (such as the customers name). For the record source of the Form, I have it as a Query to pull all the info from the Customers table and also the salesmans names from the salesman table.

How can I make a form where I select the salesman and it pulls a list of all the customers for that one salesman?

 

[Mike375]

My situation is similar to yours except I am insurance, both insurance salesmen and telemarketers. Like you I can also be both.

I have all in the DB, including the diary/follow up systems etc.

As to your problem, on the surface it sounds like your query is not updatable. Looking at your tables there should be no problem joining Salesman and Customer.

A very simple way to show all the customers related to a saleman is to have a form based on the Customer table or a query as required and then open that form where the Salesman ID in the Customer table matches the ID number on the form that you are opening from. You could for example have a drop down list of the salesmen and when you click the salesman name that inserts his ID number into an unbound text box and then uses that as the reference to open the form based on the Customer table. Do the lot in one hit with After Update on the combo box.

Do those tables have the actual fields as shown. For example, where is data such as phone mumber, address etc for teh salesmen.

 

[JohnD]

You make perfect sense and I have no idea why I didnt think of that. Now its a matter of doing it!

I love trial and error - at least I have the game plan down. Thank you

And yes, the tables have been created and all the fields are whats shown in the picture and I have yet to decide if I was going to have any other Salesman information other then the name.

Thanks!!

John D

 

[JohnD]

Thank you, I got it....had to make the control source a query and add this as a criteria under the salesman:

[forms]![Salesman_Form]![combo2]

So it simply pulls the salesmans name from my other form and filters the new form with only the salesmans customers.

Thanks again!

John D