Basics ... I feel stupid but

  • Thread starter Thread starter Dwade
  • Start date Start date
D

Dwade

I'm trying to learn Access but I'm missing something very basic in here.

I am to create a simple database to track sales.

Product Table
ProductId
ProductDescription
ProductCost

Sale Table
SaleId
SaleDate
ItemNbr Count of items sold (line items)
ProductId


Now I can create the tables no problem.

I can't create a form that will do multiple items on the same sale and show
that on the form.

It can't get much simpler than this yet I'm missing something very basic.

Help please!
 
well, few databases are ever quite as simple as they might seem to be before
you actually get into building them.

one product many be included in many sales, and one sale may include many
products. that's a many-to-many relationship, which is modeled in Access be
using a linking table which lists every product for every sale. so the
Products table lists all products, including current price. the Sales table
lists every sale, including the date of sale, perhaps location of sale,
perhaps the salesperson; in other words, fields that describe the sale - but
not the item(s) sold, and not details that describe the buyer (if you're
tracking those, you need a Customers table), or details about the
salesperson (if you're tracking those, you need an Employees table, or
perhaps a Vendors table, depends on just what your business model is). then
you need a SalesDetails table, that lists every product sold for each sale,
including product id, quantity, perhaps a size and/or color and/or weight
(unless the information is included in the Product record), and price. you
have to include price for each item, because the price of a product may
change over time. then there's sales tax, perhaps - that's a separate issue.
and if you're tracking sales, then what about returns?

as i said, never quite so simple. and the tables/relationships structure
should be based on relational design principles, and they should be designed
and built before you build queries, forms, and reports. recommend you start
at the beginning, and read up/more on relational design (normalization). for
more information, see http://home.att.net/~california.db/tips.html.

hth
 
Dwade,

Try this...

tblProducts
pProductID (PK)
pProductDescription
pProductCost

tblSales
sSaleID (PK)
sSaleDate
etc...

tblSalesDetail
sdSalesID (FK-Relate to tblSales)
sdQuantity
sdProductID (FK-Relate to tblProducts)
etc...

Your 'Count of items sold' should take place on your form or report. Sales
is a one-to-many so to get your line items you will need two tables.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
I think I'm going to just say no way and I'll focus on buying something that
will do the job. There has to be pre-built programs that will take care of
managing a sale database for a very small non profit organization
selling/auctioning donated items.

Thanks to all that looked, responded and advised. It seems Access is pretty
much beyond the beginning user for anything very useful.
 
I think I'm going to just say no way and I'll focus on buying something that
will do the job. There has to be pre-built programs that will take care of
managing a sale database for a very small non profit organization
selling/auctioning donated items.
Click to expand...

Take a look at the Northwind sample database that comes with Access. It is
specifically a sales database. It's not perfect but it should get you a lot of
what you need.
Thanks to all that looked, responded and advised. It seems Access is pretty
much beyond the beginning user for anything very useful.
Click to expand...

Its learning curve is certainly steeper than that of (say) Word or Excel, but
there are a lot of helpful resources around if you are willing to do some
fairly hard slogging:

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

A free tutorial written by Crystal (MS Access MVP):
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
 
Steve said:
Dwade,

I provide help with Access, Excel and Word applications. I charge a very
reasonable fee. I can build an Access database for you to manage sales for
your small nonprofit organization. My fee would be very modest. If you
want my help, contact me.

Steve
Click to expand...


These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
 
Steve said:
I charge a very reasonable fee. I can build an Access database for you to
manage sales for your small nonprofit organization. My fee would be very
modest.
Click to expand...

Just when you thought the snake couldn't get any lower he offers to charge a
non-profit making organisation.
 
Keith Wilby said:
Just when you thought the snake couldn't get any lower he offers to charge
a non-profit making organisation.
Click to expand...


He probably thought non profit meant a non religious organization ( non
prophet)

John... Visio MVP
 
Back
Top