I would like to do this in my mdb

[Gemini32]

Hi Folks

The following is design in Excel but want to do this in mdb...

Book1.xls
	A	B	C	D	E	F
1	DESCRIPTION	Quantity	wholesalePrice	RetailPrice	Total	Profit
2					$0.00	$0.00
3					$0.00	$0.00
4					$0.00	$0.00
5					$0.00	$0.00
6					$0.00	$0.00
7					$0.00	$0.00
8					$0.00	$0.00
9					$0.00	$0.00
10					$0.00	$0.00
11					$0.00	$0.00
12					$0.00	$0.00
13					$0.00	$0.00
14					$0.00	$0.00
15					$0.00	$0.00
16					$0.00	$0.00
17	SubTotal:				$0.00
18	15%HST:				$0.00
19				VisaFee
20	TotalInvoice:				$0.00	$0.00
Sheet1

I would like to build this in my form in mdb....I want the 15 lines down that I have in excel and be able to type in all 15 lines and 6 columns accross....I tried to embed an object in my mdb but it wouldn't allow me to type in any of it when in form view.....Also want to keep the formula's as well...I hope someone can help me figure this out, this board has awesome members and so much knowledge...I thank you ahead of time for any assistance would be very much appreciated.....

:D Gemini32 :D

 

[Corticus]

Hi Gemini,

Make a table with a primary key autonumber, then add the fields:

Description
Quantity
Wholesale_Price
Retail_Price

and define the fields as neccesary.

Then make a query, and add this table and all its fields.

Make two new expression fields in the query,
Total:[Quantity]*[Retail_Price]
Profit[Retail_Price]-[Wholesale_Price])*[Quantity]

Now make a report and put in it the above fields.
In the report footer, add 4 textboxes, for subtotal, 15HST (15% tax? Ouch!), Visa_Fee, and Total_Invoice. Add another set for profit.

Profit textbox names:
Subtotal
15HST
Visa_Fee
Total_Invoice

To calculate these totals on the report, you would add these expressions to the text boxes you just made,
in Subtotal =Sum([profit])
in 15HST =[Subtotal]*1.15
in Visa_Fee I have no idea what this is
in Total_Invoice =[Subtotal]+[15HST]+[Visa_Fee]
etc...

Let me know if you need an example.

edit: I noticed you used absolute value on your profit field. Wouldn't you want to know if your margin was negative? Seems like a pretty big difference between a $10 and -$10 margin.

HTH,

 

[Gemini32]

Hi Corticus


Absolute in Excel was the only one that would work for me the right way....But as the old saying goes the program will only work as good as the operator so I'm must be the one that needs to learn it all and master it ...hahaha...The VB's and macro's are not my strong suit but I want to learn it...Is there beginner stuff out there to learn it from scratch with no background in it...I'm a quick learner ....

The Visa Fee is the precentage of what company's add on to item when buying the product...I've only seen 3% of the subtotal....


Thanks very much for the help on my question and that would be great if you could send or put an example of it up for me to look it over to see what you mean....I'm going to work on that solution you gave me right after supper....hahaha... I know I'm addicted to the access and excel oh love the computer ...I finally amitted it...hahhah.....Thanks again have a great day...


Gemini32

 

[Corticus]

Check it out:

http://members.aol.com/cortikus/costdb.mdb

HTH,

 

[Gemini32]

Hi Corticus

Thank you

Gemini32