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

Formulas for Unit Conversion

JamesDC
P: 66
Hey all,

I started work on a way to convert my database values from lbs, to dollars but had very little luck :(.

I'm not exactly sure how formulas work in Access 2002, and if it is possible to work with numbers in a way like I would in Excel.

I've got a large DB that has in it the headings: Date, Product, Waste (lbs). These values are fed from a Data Input Form. The options for type of product on the Data Input Form come from a table called Product_Values, which has the headings Product and Value. If each of my products has a value associated with it in the Product_Values table, how can I use that to convert my lbs numbers in my main DB to dollar values? I've tried making querries to do this but I always end up with an error saying that the Tables MainDB and Product_Values cannot be related. I've also tried using a Report to do this, but once again I've had no luck :(.

Is there any advice you can offer?

Thanks in advance,
James
Mar 2 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi James

Give us the metadata for MainDB and Product_Values using the following structure.

Here is an example of how to post table MetaData :
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. Field; Type; IndexInfo
  3. StudentID; Autonumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastAttendance; Date/Time
Mary
Mar 2 '07 #2

JamesDC
P: 66
Expand|Select|Wrap|Line Numbers
  1. Table Name=MainDB
  2. Field; Type; IndexInfo
  3. Record Number; AutoNumber; Yes (No Duplicates)
  4. Date; Date/Time; No
  5. Employee Name; Text; No
  6. Shift; Text; No
  7. Product; Text; Yes (Duplicates OK)
  8. Area; Text; No
  9. Waste (lbs); Number; No
  10.  
  11. Table Name=Product_Value
  12. Field; Type; IndexInfo
  13. ID; AutoNumber; Yes (No Duplicates)
  14. Product; Text; No
  15. Value; Currency; No
  16.  
I'm not sure what you meant by IndexInfo, so I included the entries under "Indexed" on the field properties.
Mar 2 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1. Table Name=MainDB
  2. Field; Type; IndexInfo
  3. Record Number; AutoNumber; Yes (No Duplicates)
  4. Date; Date/Time; No
  5. Employee Name; Text; No
  6. Shift; Text; No
  7. Product; Text; Yes (Duplicates OK) ' this needs to be changed to reflect the ID rather than the ProductName
  8. Area; Text; No
  9. Waste (lbs); Number; No
  10.  
  11. Table Name=Product_Value
  12. Field; Type; IndexInfo
  13. ID; AutoNumber; Yes (No Duplicates)
  14. Product; Text; No
  15. Value; Currency; No
  16.  
I would change the Product field as mentioned above but the following query is designed to work with the current structure:

Expand|Select|Wrap|Line Numbers
  1. SELECT  MainDB.[Record Number], MainDB.[Date],
  2. MainDB.[Employee Name], MainDB.Product, MainDB.Area,
  3. (MainDB.[Waste (lbs)] * [Product_Value].Value) As WasteCost
  4. FROM MainDB INNER JOIN [Product_Value]
  5. ON MainDB.Product = [Product_Value].Product;
  6.  
Mary
Mar 2 '07 #4

JamesDC
P: 66
The query worked like a charm! Thank you so much.

As per your reccomendation, I don't quite understand what you mean. Could you please elaborate?

James
Mar 2 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
The query worked like a charm! Thank you so much.

As per your reccomendation, I don't quite understand what you mean. Could you please elaborate?

James
You are using the Product Name as the relationship between the two tables rather than the ID of the Product_Value table. It would be more correct to use the ID field. This means you would have a lookup field in the MainDB which would look up the Product Name and store the ProductID.

Have a look at this tutorial.

Normalisation and Table structures

Mary
Mar 2 '07 #6

Post your reply

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