X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Do I Have The Correct Layout?, Access 2016    
post Sep 13 2017, 03:23 PM

Posts: 18
Joined: 13-January 15

Hi there, I'm an Access newbie in the process of designing a new database, and I wanted to get some feedback to make sure I've got the correct layout in mind.

Database Purpose:
This will be used for one of our traveling salesman. It'll be used to plan out which customers are visited during a given week, and this salesperson will fill out a report with comments about the visit. Values to be entered in are things like customer satisfaction, sales potential, desired products, general notes, etc. When this traveling salesperson is back onsite at HQ, we'll use the database to print out a report of all the notes from each week's visit. Over the course of time, we hope to be able to print out all notes and observations on a customer basis, plus build a list of customers with more / less sales potential, etc.

The Layout so Far:
The tables I know I need:
- Customer list - contains all customer contact info
- Sales Trip List - a list of all sales trips. Contains a date for the trip (the Monday of that week is fine), a name, and a geographic region (Central NC, etc.).
- Notes - for general observations

QUESTION - do I need to have a separate table for each "class" of note? If the sales potential is measured in dollars, should all of that be in one separate table?

- Sales Trip entry form - this will create a list of sales trips and associate customers with each trip
- Customer info entry form - general contact information / lookup

This is what I'm least familiar with in Access. I assume I'll need to build a few different queries in order to get the information I need for these reports.

QUESTION - Does my basic layout seem sound? I know how to create tables and relationships, so I'm not worried about creating a functional database. Am I missing anything obvious?

QUESTION - It seems as though lookup fields are discouraged in various places. Should I try to avoid using them? If so, how do I manage sharing information between tables? (i.e., looking up a customer to associate a note to them, etc.)

Thanks in advance for your help!
Go to the top of the page
Jeff B.
post Sep 13 2017, 03:54 PM

UtterAccess VIP
Posts: 9,815
Joined: 30-April 10
From: Pacific NorthWet

I'm not sure I noticed where the list of customers to be seen on a 'trip' is kept.

Also, and probably not even necessary, what happens if more than one salesperson wishes to use this? ... or if the salesperson changes? ... or changes his/her name?

Good luck!

Lookup datatypes are discouraged around here, but lookup controls in a form are a standard approach.

Not sure what you're referring to with "class".


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post Sep 13 2017, 05:42 PM

Posts: 1,678
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL

Further to Jeff's comments, you might want to "mock up" some sample reports to confirm/identify the kinds of info to be collected. This would also apply to Notes/Comments you intend to collect. This can be a great analysis tool and get you some feedback/criticism before doing too much "coding and guessing". Free form text fields can often be difficult to relate/quantify, so, you may want to have some predefined responses (some agreed upon scale) that can be selected by salesman/men. This might apply to Customer Satisfaction, Sales Potential etc. This could simplify reporting with consistency.

Sometimes it is a helpful learning exercise to describe the purpose of your proposed database in simple terms. Start at the 30,000 ft level and gradually add detail. You can bounce/share the evolving description with colleagues (salesmen, potential users, those who need the reports etc) to get feedback. This will help with communications and will ensure clarification of your facts and terminology.

Avoid multi-valued fields and lookup fields in tables.
This post has been edited by orange999: Sep 13 2017, 05:44 PM

Good luck with your project!
Go to the top of the page
post Sep 14 2017, 07:05 AM

Posts: 18
Joined: 13-January 15

I'm not sure I noticed where the list of customers to be seen on a 'trip' is kept.

Is this something that should be separate from the list of trips? i.e., so there's another table that has a list of trip numbers with customer reference #s?

Not sure what you're referring to with "class".

This is related to the notes. Instead of keeping one long-form text note, I plan to break down the information I want to get into smaller bits like Orange999 suggested. i.e., Using radio buttons and yes/no questions to help classify customers. "Is there potential for increased sales?" "Does another salesperson need to follow up with customer?"

I'll try to do a mock-up today and see how far I get.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    26th September 2017 - 04:56 PM