Question

[Nicolette]

First off I'm sort of a newbie, I mean I have made some very basic databases before but I am now in need of a more advanced database. So please be patient with me


ok quick overview of my needs: I need a database to control my inventory and allow me to create and email invoices. My product is listed on ebay so I will have listing numbers and buyer-id's to deal with but I also sell on a normal website so not all sales will have buyer-id's and listing numbers. Honestly I am not overly concerned with the listing aspect and the buyer-id's so if they must be left out that is fine by me. I would also like to eventually keep track of my cash flow but I believe a seperate database would work better for that aspect, correct?

anyway on to the question. I just need to know if my set up is correct or if i'm atleast on the right path so here are my tables

CUSTOMERS
(PK) CustomerID
FirstName
LastName
BillingStreetAddress
BillingCity
BillingState
BillingZip
BillingCountry
ShippingStreetAddress
ShippingCity
ShippingState
ShippingZip
ShippingCountry
Phone
Email
Website
Comments

INVENTORY
(PK)InventoryID
SKU
Title
Description
Size
Color
Material
Manufacturer
Supplier
StockLevel
ReorderLevel
StockLocation
Discontinued
DateAdded
Picture

INVOICE
(PK) InvoiceID
InvoiceNumber
CustomerID
Status
InvoiceDte

ORDERS
(PK) OrderID
CustomerID
OrderDte

PAYMENTS
(PK) PaymentID
PaymentAmount
PaymentDte
Method
InvoiceID

It is my understanding that I should also have an INVOICEDETAILS table and a ORDERDETAILS table that would contain the more specific data but this is where I get lost. I understand the Primary Key and Foreign Key concept of linking the tables to get the data but i'm missing something somewhere in the process

Also I would like to have the workflow go as follows:
1) Enter customer information (I am wondering if importing from my paypal transaction spreadsheets would make this easier?)

2) Have a form that has a drop down box that allows me to select the customer ( I would like to be able to start typing the name or the ID and have it narrow the list as I type if that is possible) and autopopulate the remainder of the customer's information

3) I guess it would be a subform that allows me to create their order (again i'm thinking a dropdown that narrows the options as I type for each Line of product)

4)Since payment is basically immediate I just want a way to record the payment so that the invoice status will be changed to paid

5) I would like to be able to email them the invoice but to also print a packingslip which I was thinking mailmerge with Word would do that part nicely.

6) I would like to be able to have a summary type window that shows me which orders are paid, which are not, which have been shipped, which are waiting to ship, (if this can be done to accommodate the Auctions I would also like to see which ones I still need to leave feedback for and which one's I have received feedback for.)

Another note on the auction aspect, I am going to start using eBays flat file submission method so if there is a way to choose the products I want to list and create the CSV file from Access that would just ROCK!

This may sound insane to some of you but my thoughts on making this database is to make my business as efficent and as automatic as possible I'm a full time student and a mom of 4 kids ages 10 years to 5 months and I work part time, But my business is the real money maker. So i'm sure you can see my need to make this database and the entire process as automatic as possible. Like clicking a button and importing my reports from Excel and clicking a button to print all packingslips and email all invoices at one time, and at a glance see what is done and what still needs to be done. oh and mailing labels would be nice too!

Oh yeah at my daughters request she wants a way to clock in and out ( i pay her $1.00 an hour to help me with the orders) but she thinks i'm scamming her LOL! I'm not but i think she is trying to scam me!

Thank you so much for any and all help you can provide! I know this isn't going to be an easy task and i'm ready for the challenge!

Nicolette

 

[PNGBill]

Welcome Nicolette

Your post is very detailed and you appear to have a good understanding of databases although, very hard to answer all questions and some will lead on from others.

You are advised to have OrderHeader table and OrderDetail Table.

Should not need to have Order and Invoice tables as one can do both.
An Invoice is an order that is at another status.

Your general Form layout will be MainForm, Header and SubForm Detail.

Create One "Form" and have Command Buttons to "View" existing Orders, "Add" new Order. the command Button will change the Forms Property for AllowAdditions, AllowEdits etc.

 

[Nicolette]

oh yes I am expecting to have many more questions I took a very basic Access class last semester so I think I have a pretty good understanding of the basics of a database.


Ok let me rephrase to make sure I understand your advice.

I don't need both the order table and the invoice table ok that makes sense.

OrderHeader table would contain what? the OrderID (PK), CustomerID, OrderDte?

OrderDetail table would contain the InvoiceID (PK), InvoiceNumber, CustomerID, and Status

and the form layout would be
MainForm being the Customer's information
Header being the data from the OrderHeader table as mentioned above
SubForm Detail being the data from the OrderDetails table as mentioned above

I understand the command button you mentioned.

Is that all correct? or did i misunderstand?

 

[PNGBill]

Very good. except, status would be on OrderHeader.

You could build in Back Order which would require a field on the OrderDetail but still the Main issue of Status would refer to the OrderID being for an Order that is either NewOrder, Invoiced, Delivered ?

Don't worry about "Paid" as this is a calculated issue.

 

[Nicolette]

ok great I made the adjustments, Most everything is still on paper at this point I'm working on the database as we speak so my next thought is I would use a query to pull the data from the Inventory table and include a where clause to block discontinued items from displaying is the correct? and setup the relationships.

 

[Nicolette]

Ok i have attached my relationship report for what I have so far if you see anything that is wrong or could be done differently, please let me know

 

[PNGBill]

I would opt for all data to be in the query and use a command button to filter the discontinued records or better still, have the filter set in the open open event of the form and have a button to remove the filter should you wish to view discontinued items.

having all records in the query will mean you can use the same query for many different forms, just filter as you need.

 

[Nicolette]

ok i'm lost again.... I set up the tables and their relationships as shown in the attachment above. I started on the form but that is where I got lost. so I need to break this down a bit more

I need to creat a subform from the table Inventory and have it like a datasheet so there is a dropdown box in the SKU field and the rest will populate defaulting to a quantity of one (since that is the usual number per Item) This will go in the details section in design view

I need to create an area where the customer's information will autopopulate by their customer number

I need an area that shows the OrderID (PK), CustomerID, OrderDte

and I also need an area that shows InvoiceID (PK), InvoiceNumber, and Status

for some reason I can not wrap my head around that... Help please

 

[Nicolette]

ok great idea never thought about doing it that way!

 

[PNGBill]

Relationships.

OrderDetails just linked to OrderHeader.

OrderDetail shouldinclude OrderID and be joined to OrderHeader by same.

How do you handle Payments. By Order or by Customer. Maybe Customer for your type of business. Join looks ok.

Good idea is to Name TblOrderHeader, FrmOrderHeader, RptOrderHeader etc

If you always start your Query, Table, Form, Report, Command, Macro etc with Qry, Tbl, Frm, Rpt, Cmd, Macro etc you have less confusion later.

 

[PNGBill]

Your Main Form for Orders is from TblOrderHeader and the SubForm is from TblOrderDetail

 

[Nicolette]

Yeah I know about the naming i just forgot oops...
Yeah my payments are by customer

ok I updated the tables and the relationships is this what you meant?

as far as the form goes I'm getting lost at is how I or where I put in the products they ordered. do I need to add something to the tblOrderDetails?

 

[G37Sam]

If customers are allowed to buy more than an item at once, you're looking at an OrderDetails table (to act as a subform later on). Orders will have a one-many relationship with OrderDetails by OrderID

 

[PNGBill]

You still have the line between tblOrderDetails and tblCustomers - remove this and also, remove the CustomerID from tblOrderDetails.

CustomerID is in tblOrderHeader - that is all you need.

 

[PNGBill]

tblOrderDetails should include ProductID from your tblProducts - I didn't pickup on this.

Include tblProducts in your relationships.

 

[Nicolette]

Ok I took care of the relationships Yes!ok now that makes sense I just wasn't seeing how I got the acutual product. Like I said I can do basic with minimal relationships and such I get lost in all the stuff floating around in my head as i'm working after the database starts growing.... Ok I'll get to work and i'll let y'all know thank you so so much!

 

[Nicolette]

ok here is updated relationships Thanks I'll let you all know how it goes.

 

[PNGBill]

tblInventory should only be joined to tblOrderDetails, in this picture.

later, if you add tblPurchases then there will be a join between that and tblInventory.

Always be thinking about the least number of joins possible.

Any other relationships can be achieved through Queries. eg if you need data on what customers have ordered a given Sku then your query will find this via tblOrderHeader & tblOrderDetail.

Just noticed, tblInventory should have Sku as it's first field and primary key. The join to tblInventory is by Sku. tblInventory shouldn't have InvID.

Also. tblOrderDetails should have OrderDetailID as it's first field and Primary Key and InvoiceID is just a field and of course is the joined to tblOrderHeader.

 

[PNGBill]

tblInventory. Sometimes it is best to have an AutoNumber as the Primary Key and the Document Number as a field.

Primary Key is the Unique Identifier and doesn't need to be the Join field.

In the Case of tblInventory, If you have an AutoNumber as the Primary Key and then have Sku as the next field you have the flexibility of being able to give the Sku a range of numbers that may suit your product range. You still set the field to no duplicates etc but it is not the Primary Key and doesn't show on your invoices.

Often commercial software uses an Invoice Number that is also not the primary key. The user never sees the primary key.

Auto Numbers effectively can never be changed and will often appear to skip numbers in the sequence and if this is an issue (InvoiceID/OrderID then add an additional field to tblOrderHeader tblOrderPK - an AutoNumber.

tblOrderDetails primary key, not so important as Sku & Order Number as customers will never see the order detail number as this is "In House"

This make sence??

 

[Nicolette]

Well I got lost between your last 2 posts

The sku is thusfar unique to each product I have new suppliers I will be working with and am planning on expanding my inventory I don't really forsee and duplicate sku's.

So should the sku be the PK and the join field?
I understand what you mean by the tblOrderDetails PK being "in house" But also I was only planning on the customerID and InvoiceID or InvoiceNumber being visible to the customer (I had planned on entering the InvoiceNumber from the invoiceing system I currently use until I had the invoicing automatic through the database at which time I had planned on using the InvoiceID as an autonumber to create the InvoiceNumber I guess I would just delete the field at that time and export the old invoices or archive them).

Also yes I would like to incororate a tblSales or tblPurchases I figured it would contain just the InvoiceID (InvoiceNumber), CustomerID, and whatever else it needed to have.