Use Access for Invoicing -

[deSiguy]

I need help in creating an Access database. I am beginner in Access but could find my way around.

I think I need to have 3 tables in the database that I am creating. So far here is how I have set up 3 tables.

CustomerT - Customer info (name, birth date, address, etc.) Primary key CustomerID, Secondary key ProductID. I have one other field in this table called FileNum - this is customer number I assign on paper form. All the customers have one unique file number.
ProductT - This table has Yes/No choice for all product (say 10). Yes means charge customer for that produce and No means don't. Each record is ties to specific customer.
Primary key ProductID. I also have FileNum field in this table and CustomerT and ProductT are related by this field.
ProductInfoT - this table has details about the product. Product name, numerical code and price.


Let's say for Customer 11111 (FileNum). I would have customer name, birthdate, address etc in CustomerT. In ProductT, I would have following.
Product 1 Yes/No
Product 2 Yes/No
...
...
Product 10 Yes/No



and In ProductInfoT
ProductInfoID Product Name Product Code Price
1 Product 1 99999 $10
2 Product 2 99888 $12
...
...
10 Product 10 99777 $15


Now I would like to generate an Invoice for Customer 11111. It should look in ProductT, pick only products with "Yes" or Check mark, pick corresponding product details from ProductInfoT and sum price for total cost.


In order to what I would like to achieve, Is 3 table set up that I mentioned appropriate or is there a better way to do it?
Finally, What do I need to do to generate final Invoice?


Thank you.

[June7]

This is not appropriate structure for a normalized relational database.

Suggest you complete a full introductory tutorial on Access. This site might help: http://www.rogersaccesslibrary.com/

[orange]

I totally agree with June. The tutorial at RogersAccessLibrary will help with concepts and design.

But I also recommend watching the free videos on this page

[deSiguy]

Thank you both of you. I have started recommended reading and watching videos. I think this will help me learn a lot. Thanks again.

[deSiguy]

After spending some time learning database design and normalization, I have reached to another stop. Need some help/pointers for next steps.



Again, here is what I am trying to achieve.
I need to have a customer database with all customer info. Second, I need to print an invoice based on different products purchased out of fixed 20 products by each customer. Likelihood of adding more products is minimal. I could have multiple (2 to max 4) Invoices per customer.


For the products order form, I would wish to have check box for each product in place of drop down menu. So to indicate products purchased, I would just click on the check box.

Question:
> Is the database I created so far look right?
> What I don't understand is how to create an order with product check boxes and how to generate product and total for invoice report!!
> What are next steps?

Thanks

[darshit_goswami]

some advise ,.. not sure if its correct in your case...

First ,.. why you want yes/no field ? Better use a combo box..

read here... http://allenbrowne.com/casu-23.html

Next , if i m getting it correct then your serviceT == products ...

in that case its easy to create invoice ,..

here r the steps i would do ..

Create a sub form for Order details and get it attached with Orders table

Then I would build a query which will get all the info i need for report ( creating invoice )

and finally, I will create a report based on that query.

P.S. - if possible, google for northwind database (its free) and spend some time with it..

[darshit_goswami]

I use checkboxes but only when there is no other way around. Or I want operator guy to get little lazy ( in my case my storekeeper ain't computer savvy, need to give him buttons or checkboxes to click ) .. but that too just for optional reports which he needs time to time based on different criteria. Other then that I prefer to have a combo box with two options "yes" and "no" in it.. its easy to filter and also looks more promising.

[orange]

Your dateofVisit1,dateofVisit2,dateofVisit3 indicate non normalized structure. If visitDate by Customer is important, then is a Visit always associated with an Order??? do you record a Visit even if there is no Order???
Do you need to record visitDate separately?

[deSiguy]

Thank you Darshit and Orange,

In my case I think combo-box might not be considered as easy as check box by couple of other users. (Not computer savvy)
I will spend some time provided and your hints. I have spent little time on northwind database. It's little bit too much for me, but will spend more time to see if that helps.

Orange, date of visit may not always mean order on those days, it might. first visit almost always will have an order. The only other reason I would like to save date of visit is for record. I will consider normalization theory about those fields.

Thanks guys.

[darshit_goswami]

well in such case,..

U can try vba editor. and do something like

if me.chkProduct1.checked = true then

do something

end if

and repeat this for all your products ,.. but still its not viable. It wont survive in a long run.

[orange]

deSiguy,

Orange, date of visit may not always mean order on those days, it might. first visit almost always will have an order. The only other reason I would like to save date of visit is for record. I will consider normalization theory about those fields.

How /why would you record the visit when there is no sale? I guess it depends on the "business", but in many situations "Visit" represents some interaction.

[deSiguy]

Good Morning,

I am making progress with great help on this forum. Thanks to June, Orange and Darshit.

@Orange's point: After internal discussions, I have decided not to track date of visit separately. So all the dates are removed from Customer table.
@ Darshit's point: I have also decided to go with Combo-box (Similar to Northwind database - Order form) I am working on your suggestions.

Along with questions I have asked here, I have been making progress in other small areas of how to design forms, buttons, etc.

Q1: I am trying to replicate "Customer Details Form" similar to Northwind database. I am able to create a General tab and Orders tab. But when I add "Customer orders subform" on second tab, it seems it is adding same customer record by number of records. Normally when you click next record on general tab, it should go to next customer record on general tab and bring all the orders/invoices for that customer on Orders tab. But when I add customer orders subform, and I click next record - it basically keeps same record. I would get same record for equal number of time as number of orders for that customer. How to fix that?

Q2: In OrderDetail Table, I have Unit price. I am not planning to add any complication of discount rates and qty is always going to be 1. Do I even need Unit price in OrderDetail table? If yes, How do I populate that field?

Screen shots from tentative form design in powerpoint.



I know I have more questions. However I will work on recommendations in this forum one at a time to learn and resolve my issues.

Thank you.

[deSiguy]

I was able to answer my Q1 by following Northwind. Now on to number 2.

[June7]

Let's assume that prices can change over time. Two options to assure that pricing for existing records are not altered in the future.

1. save the unit price into OrderDetail record - this requires manual data entry or code

2. create a new record in Products table for each price change and flag the old price record as 'inactive' and exclude them form combobox list for new OrderDetail record entry