HELP!

[accessnewbie79915]

OK I admit that I am a beginner and know next to nothing about Access. My
boss is asking for me to create a database and include some relational,
indexing, and sorting options that seem pretty basic to me. However, I am
quandried as to how to accomplish this. Let me break down the project here: I
am creating a customer database. This database needs to include the customer
ID set as the primary key. Then I need to include the year the customer was
active, for example 2010. Then I need to include all the customer data, such
as first name, last name, address, city, state, zip code, area code, work
phone, home phone, cell phone, category of items transferred, list of items
transferred, as well as the date of each transaction. My boss wants me to
build this database in such a way as to eliminate repeat and duplicate data,
as well as having all the appropriate information related to one another. He
wants all the information properly indexed. He also wants all the data to be
able to be sorted be each individual information field, such as: let's say a
customer purchased a food item, he wants a sorting option that will pull up
only the customers that purchased food items. I do apoligize for the length
of this post, but I feel being as detailed as possible will be helpful. I
have begun reading the Microsoft Press book for access 2000. I am working
with access 2002. I have gone online and downloaded e-books, as well as video
tutorials. Each one of these things has been very helpful, and I have gained
a basic knowledge of how to work with access. With all that in mind I am
still lost when it comes to tackling this monster of a database that my boss
as asking for. Any help with this would be greatly appreciated. Also if you
know of any templates that would work well and include all the pertinent data
listed above, then please forward a link. Thank you in advance for all of
your help. May God richly bless all of you.

 

[John W. Vinson]

OK I admit that I am a beginner and know next to nothing about Access. My
boss is asking for me to create a database and include some relational,
indexing, and sorting options that seem pretty basic to me. However, I am
quandried as to how to accomplish this. Let me break down the project here: I
am creating a customer database. This database needs to include the customer
ID set as the primary key. Then I need to include the year the customer was
active, for example 2010. Then I need to include all the customer data, such
as first name, last name, address, city, state, zip code, area code, work
phone, home phone, cell phone, category of items transferred, list of items
transferred, as well as the date of each transaction. My boss wants me to
build this database in such a way as to eliminate repeat and duplicate data,
as well as having all the appropriate information related to one another. He
wants all the information properly indexed. He also wants all the data to be
able to be sorted be each individual information field, such as: let's say a
customer purchased a food item, he wants a sorting option that will pull up
only the customers that purchased food items. I do apoligize for the length
of this post, but I feel being as detailed as possible will be helpful. I
have begun reading the Microsoft Press book for access 2000. I am working
with access 2002. I have gone online and downloaded e-books, as well as video
tutorials. Each one of these things has been very helpful, and I have gained
a basic knowledge of how to work with access. With all that in mind I am
still lost when it comes to tackling this monster of a database that my boss
as asking for. Any help with this would be greatly appreciated. Also if you
know of any templates that would work well and include all the pertinent data
listed above, then please forward a link. Thank you in advance for all of
your help. May God richly bless all of you.

This may not be as hard as you think. The key is that you need to identify the
"Entities" - real-life persons, things, or events - of importance to your
application. Each kind of Entity will have its own table. Some of the entities
I see here are:

Customers - CustomerID (autonumber primary key, unless you have a unique
manually assigned ID), LastName, FirstName, address, etc - just biographical
and contact information, NOTHING about purchases)

Items or Products - a table of what you sell, with ItemID primary key, and
other fields describing the item as a thing in its own right; include an
ItemType field

ItemTypes - a tiny table with one row for each type of item you want to track

Purchases - a table with links to the CustomerID, the ItemID, and other fields
about an individual purchase (quantity, sale date, quantity, amount charged,
etc.)

Here are some references that may require a bit less digging than your big
books: Crystal's video may be a good start.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[KARL DEWEY]

Here are some thoughts on table structure. Create one-to-many relationship
between table and select Referential Integerity and Cascade Update. Use
form/subform for the one/many display and data entry. Use a combo box to pick
products and set AutoExpand property to Yes for type-ahead entry.

tblCustomer --
CustID – Autonumber - primary key
Activated – DateTime
FirstName - text
LastName - text
Addr1 - text
Addr2 - text
City - text
ST - text
ZipCode - text
Plus4 - text
WorkPhone - text
HomePhone - text
CellPhone - text
Pager – text
Discount –
Etc.
Not ‘sorting’ but search criteria. You will need to have a field for
category for this purpose like this –
tblProduct –-
ProdID – Autonumber – primary key
Product – Text
Category – text – maybe build a table of categories and link so as to avoid
typos or multiple variations of the same category (Food, canned peas, peas,
prepared peas, Blackeyed peas, etc.).
Size –

tblCustPurch –
PurchID - Autonumber - primary key
CustID – Number – Long integer - Foreign key
ProdID – Number – Long integer - Foreign key
QTY - Number – Long integer

tblPricing –
PriceID - Autonumber - primary key
ProdID – Number – Long integer - Foreign key
Price – Currency
PriceDate – DateTime – Date price posted