Newbie relationship question (rather long)

V

Veli Izzet

Hi,

I have a table of products. (Products)

Now I want to be able to calculate the cost of each product.

The problem is, for each product, the number and types of ingredients
are different.

The recipe may consist of 3 items, or 6-7-8 items.

I tried to put 24 different fields with each item for the products table
(I know this is clumsy) (8 items-name,amount,unit price) and do the
calculation on the forms and reports, but when a field is empty the
calculated fields on the forms did not work.

It seems that I need to have another table, "costs", and it must be
joined by a one-to-one relationship with the products table.

I tried to create this, but everytime I can only create one-to-many
links with the wizards.

The questions:
1-Am I in the right direction?
2-How do I create the one-to-one relationship?

Thanks for answers.
 
G

Guest

Dear Veli Izzet

Please make me clear with your table products, what fields it has and what
type of info are there.

Like This: Table Products
Fields
 
V

Veli Izzet

Table: Products

Fields
--------
ID (Autonumber)
PCode
PType
PDetail1
PDetail2
PName
PImage
PWhere
PSituation
PPrice (number)

All the fields not designated are text

I have one form with calculated fields that are derived from PPrice

PPriceVAT = PPrice*1.18
and
PPriceEuro = Pprice*1.3

The products are semi-precious jewelery,

Some products are made of silver, some silver, one or more semiprecious
stones, etc.

Some products are only put on a string, some products undergo more than
one process like gold coating or whatever.

Each product has one certificate and one packaging. (For export there
may be other packaging expenses).

Overhead is assumed to be 20%.

I think, I have to create a new table like:

Table: Costs

Fields
-------
CostID
CostItem
CostUnit
CostUnitprice

and link two tables with

Table: Link
Fields
------
ID
CostID

and put it in a form/subform combination.

Then create few reports, including the calculated fields.

But how?


Thanks for your help
 
G

Guest

Dear Veli Izzet

As I understand you need to get the price for each product right?
if it's so why don't you insert the ProductQuantity coloumn? (PQuantity)
And you can just use the following <<<PPrice*PQuantity>>> in a textbos
whenever you want in the form or in a report.

If I miss something just let me know.

As to the relations: you can relate your tables using the Primary Keys
(think it's the better way). you have two tables Products (with product
details, descriptions, price) and another table Costs (with what info?) I
mean what you want to define there? > CostID -
CostItem - What Item you want to place there?
CostUnit - What Unit?
CostUnitprice - What you mean

You can relate many tables in Access for instance Products - ID related to
Costs - CostID, you can enter the same numbers in those fields or you can
choose the relationships from database toolbar there will appear a Window
named (Show Table) from where you can select and add you tables and by
dragging the necessary fields from one to another. that's it. you have
related the two tables, but first you must define what type of datas and what
fields should be related, it's important.

Please let me know what you've done

and one thing before you start the modifications just make a copy of your
databaze file :)
 
V

Veli Izzet

No, I do not want the price,

I want to calculate the unit cost, not the price.

Some more explanation:

For product one:

I need 25 cm of string (.1 cents), 1 gr. stone1 (5 usd) and 25gr. metal1
(3 usd), and they must be polished (.25 cents), and cut (.50cents)

For product two:

I need 30 cm of string(.1cents), 2 gr. stone2 (1,2 usd), and they must
be warped (1 usd).

According to the costs, the program will calculate a price.
 
G

Guest

Veli:

Alex is on the right track. What about something like this:

tblProducts
ID (Autonumber)
PCode (PK) (Assuming this is unique to each item.)
PType
PDetail1
PDetail2
PName
PImage
PWhere
PSituation
PPrice (number)

tblCost
CostID (PK)
CostDesc
CostAmount

tblProdCosts
PCode (PK) (FK)
CostID (PK) (ID)

Then simply add all the related records in tblProdCosts to get your unit cost.

Sharkbyte
 
V

Veli Izzet

Sharkbyte,

This is what I am trying to do, and it also seems straightforward but,
somehow I cannot do it.

Another question: Can I add a spreadsheet to a form and do the
calculations there? In the form design window I added office spreadsheet
to the toolbox, but it is greyed out.


It seems I am stuck..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Relationship Issues 4
Combo Boxes 1
Relationship problem 1
Newbie question on databases 1
Relationship question... 6
Relationship problem 2
Using a Field to contain a LIST 1
MSDS Database 2

Top