Real Beginner but in need of help with coding

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok i have no idea how i am going to explain my problem, here goes

I am creating an internal database for my stock of a clothing company.
I have created tables with fields in style number, colour, unit price, sizes
(one cell for each size), total quantity and total value.

This data is transfered to a form which shows how many pieces of each size i
have quantity and total value of this style.

Now the problem. I want to create another form that will display the total
number of pcs for all the styles and total monetary value for all. When i try
to do it through a query it keeps coming up with a value of 0 or $0.00. I
think the reason for this is the data of the cells of total and total value
are formulas already.

i.e total = value of numbers in each size cell.
total value = total times unit price.

I hope i have not lost you.
i am very new to this dont realy want to start again. just want someone to
help me put a formula in for calcualting the total values for all styles on
anohter form

thank you so much for your time

Raveen
 
Raveen

You've described what sounds a bit like a spreadsheet -- is there a reason
you couldn't use a spreadsheet to do what you want?
 
Send me the .MDB file with a minimum amount of data in it and I will take a
look at it. covrambles AT yahoo DOT com
 
:

I am creating an internal database for my stock of a clothing company.
I have created tables with fields in style number, colour, unit price, sizes
(one cell for each size), total quantity and total value.

This data is transfered to a form which shows how many pieces of each size i
have quantity and total value of this style.

Now the problem. I want to create another form that will display the total
number of pcs for all the styles and total monetary value for all. When i try
to do it through a query it keeps coming up with a value of 0 or $0.00. I
think the reason for this is the data of the cells of total and total value
are formulas already.

i.e total = value of numbers in each size cell.
total value = total times unit price.

I hope i have not lost you.
i am very new to this dont realy want to start again. just want someone to
help me put a formula in for calcualting the total values for all styles on
anohter form

thank you so much for your time

Raveen
Click to expand...

As Doug said, you are using a spreadsheet approach.

In Access, it is a bad idea to put calculated values in tables. Just use
queries and calculate them on the fly.

Might I suggest three tables
tblClothingItem
ItemID- primary key, autonumber
ItemNumber -- text
ItemName -- text
Color -- text
Size -- text
Quantity -- Number (long)
UnitPrice -- Currency

tblColor
Color -- text, primary key

tblSize
Size -- text, primary key

A query using all the fields of tblClothingItem. Name it something like
qryAdjustInventory. Use the autoform based on that query to create a form to
enter inventory when you receive items and modify when you sell something.
To save typing, put two combo boxes to select color and size. Let the wizard
step you through.

A second query called qryViewInventory which uses all the fields from
tblClothingItem plus a calculated field: Value: [UnitPrice]*[Quantity].

You have two options here:
Base a tabular form on qryViewInventory. Put two text boxes in the footer
of the form, Total Quantity =Sum([Quantity]) and Total Value =Sum([Value]).

To get better display of information, I prefer using the Report Wizard to
make a report grouped on ItemName. That report will give you detail
quantitly and value on each size and color of item, total quantity and value
for each item and grand total of quantity and value. Once you have the basic
report, you can modify it to show what you need. For instance, I don't see
that total quantity tells you anything while quantities for each item or item
detail are needed.

Good luck,
Roxie Aho
roxiea at usinternet.com
 
Back
Top