By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,334 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Help Creating an Order Form/Invoice

P: 6
Hi All,

I am new to MS Access 07, & do not know any VB, on a new business adventure. I am running Office07 on Vista Home Premium.

I am in the process of creating a database that in the end I will be able to create quotes, bill out customers for security services, cctv products and installs,create purchase orders to my distributors being that I keep no inventory on hand, and track payments such as deposits on equipment.

What I need help with is creating an invoice I have tried and been looking everywhere for help. I know I need 2 more tables; OrderMaster, & OrderDetails; What I would like to know is how exactly do I need in the columns labled and how do I get it transfered to an invoice. In the Details part I would like to have the Product#, Quanty, Descript, OurPrice, Line Total.

Below is the tables I have already created. Any help or examples that anyone is able to provide would be greatly appreciated. Thank you in advance for help.

CUSTOMERS
CustomerID(PrimaryKey[AutoNumber]); CompanyName; Prefix(Look-up from "Prefix" Table); FirstName; LastName; Address; City; State(Look-up from "State" Table); Zip; PhoneNumber; Ext; CellNumber; FaxNumber; E-MailAddress; NewsLetter; Notes; CustomerSince; Referred; CustomerType(Look-up list); TaxExempt(Look up from Yes/No Table)

DISTRIBUTORS
Distributor(PrimaryKey); Contact Name; Address; City; State(Look-up from "State" table); PhoenNumber; Ext; FaxNumber; CellNumber; E-MailAddress; Website; Notes(Memo field); Terms(Look-up from "Terms" table); Limit

PRODUCTS
Prouct#(PrimaryKey); Manufacturer(Look-up from "Manufacturer" table); Distributor(Look-up from "Distributor" table); Category(Look-up from "Category" table); CCD; Lens; IR(Look-up from "Yes/No" table); #ofLED's; MinLux[Color]; MinLux[B/W]; MinLux[Sens-up]; Resolution[Color]; Resoltion[B/W]; OpticalZoom; DisgitalZoom; Style(Look-up from "Style" table); Control; AutoIris(Look-up from "Yes/No" table); Varifocal(Look-up from "Yes/No" table); Motorized(Look-up from "Yes/No" table); Day/Night(Look-up from "Yes/No" table); OutDoor(Look-up from "Yes/No" table); Heater(Look-up from "Yes/No" table); Fan(Look-up from "Yes/No" table); VandalProof(Look-up from "Yes/No" table); Mount(Table contained look-up list); CH; HDD; FPSRec; Format(Look-up from "Format" table); Media(Look-up from "Media" table); LAN(Look-up from "Yes/No" table); USB(Look-up from "Yes/No" table); AddtionalInfo(Memo field); SpecSheet(Attachment); Disontinued(Yes/No check box); MSRP; OurPrice; Cost; WorksWith(Look-up from "Product#" query); Desript

Employees
UnitID(PrimaryKey); FirstName; LastName; CellNumber; DC#; NYSecID#; ExpDate

I also have the following tables which are pretty much self-explanatory:
"State", "Prefix", "Yes/No", "PayType", "Terms", "Category", "Style", "Format", "Media", "Mount"

I apologize if this is to much info posted; if anyone would like to see my database please let me know I will be more than happy to send it. Once again I would like to thank everyone in advance for any help you can provide.

Joe M
Oct 23 '08 #1
Share this Question
Share on Google+
6 Replies


mshmyob
Expert 100+
P: 903
Ok big job but assuming you have everything else working your two tables called Order and OrderDetails hold all the data about a specific order (invoice).

The Order table would have at a minimum the fields of:
OrderID (PK)
OrderDate
CustomerID (FK)

Your OrderDetails table would hold the detail of each line item for the Order:
OrderDetailID (PK) - make this an autonumber
OrderID (FK)
CustomerID (FK)
ProductID (FK)
OrderDetailSalePrice (picked up from product table - must store here in case of price changes in future)
OrderDetailQTY
OrderDetailDESC (your choice - again in case you change descriptions in future - picked up from product table)

Depending on how you do taxes that would have to be taken into effect also but I don't know enough of your business rules.

really before you start you need to identify ALL you business rules. You should also read up on normalization.

cheers,
Oct 23 '08 #2

NeoPa
Expert Mod 15k+
P: 31,273
Normalisation and Table structures should help with that.
Oct 23 '08 #3

P: 6
OK... I have created the "Orders" table w/ the following fields
OrderID(PK)
OrderDate
CustomerID(FK)
Salesman(FK)
ProjectedInstall

Sales Tax is 8.375% for services and goods.

For the "OrderDetails" table how do I get it to pull up the info such as product price and descript??

I want to thank you again for your help.

Cheers
Joe
Oct 23 '08 #4

mshmyob
Expert 100+
P: 903
A number of ways to get the price and description. A simple way is to make a combo box that looks to your ProductID in your product table and include the price and description fields. In the combo box hide the price and description and then in the AFTER UPDATE event of the combo box you can just refer to the combo box columns for the price and description and store in your OrdersDetail table.

or

You could just create the combo box to the productid in the product table and in the AFTER UPDATE event you could do a DLOOKUP in the product table for the price and description.

As for the tax if you only have one tax rate for both goods and services and it is always applied to all goods and services the percent value can be stored in the Order table. Again store it for the order because it may change in the future.

cheers,

Sales Tax is 8.375% for services and goods.

For the "OrderDetails" table how do I get it to pull up the info such as product price and descript??

I want to thank you again for your help.

Cheers
Joe
Oct 23 '08 #5

P: 6
I don't fully understand how to set up the AFTER UPDATE event and how to do a DLOOKUP. Can you please explain a little more.

As far as the tax rate you want me to create another field on my "Orders" table, correct??

Once again thank you.

Joe
Oct 24 '08 #6

mshmyob
Expert 100+
P: 903
For the tax rate yes you would create a new field in the Order table and store the tax rate for there. That way you can calculate the taxes paid for that order.

In the properties of your combo box you will fnd an EVENT tab and in that you will find an AFTER UPDATE event. In that event go to the code builder (press the 3 little dot button) then your code would be something like so:

Expand|Select|Wrap|Line Numbers
  1. dim dblProductPrice as Double
  2. dim strProductDesc as String
  3.  
  4. dblProductPrice = DLookup("[ProductPrice]", "tblProduct", "[ProductID] = " & Forms!frmYourForm.cboProductID.Column(0))
  5. strProductDesc = DLookup("[ProductDesc]", "tblProduct", "[ProductID] = " & Forms!frmYourForm.cboProductID.Column(0))
  6.  
  7.  
Where
ProductPrice and ProductDesc are the names of the fields in your Product table. tblProduct is the name of your Product table. frmYourForm is the name of the form where the combobox control is. cboProductID is the name of your combobox control.

cheers,


I don't fully understand how to set up the AFTER UPDATE event and how to do a DLOOKUP. Can you please explain a little more.

As far as the tax rate you want me to create another field on my "Orders" table, correct??

Once again thank you.

Joe
Oct 24 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.