Need help with this database - microsoft.public.access

I'm building a database who's end goal is to calculate prices for different projects. My first table has a list of all the separate materials I'll be using and the cost for that particular item (eg Nail, 2x4, plywood sheet, etc). The key value for that table is the Resource Name. The second table has the specific project names along with the qty of the materials from table 1. The main form for this table allows me to create a project, select building materials from a predefined list, and add the quantities and everything inputs into the table correctly there. The Key value for that table is Project. I can't figure out if the next step is another form or query. I want to type in or select "Bird House" and have Access show the following: Bird House Nails x8 $0.40 Plywood x1 $1.20 Varnish x1 $3.50 Total Price: $5.10 I know if I was using Excel the formula would look something like this: =SUM((B2*'Resource List'!B8)+(B3*'Resource List'!B11)+(B4*'Resource List'!B12)) But I have no idea how to get that into an Access.

If I understand correctly, just add both tables to your record source and join on the primary and foreign fields. You can then create a calculated column (or expression in a control source) that multiplies the quantity times the cost. -- Duane Hookom Microsoft Access MVP "Dan" wrote: > I'm building a database who's end goal is to calculate prices for different > projects. > My first table has a list of all the separate materials I'll be using and > the cost for that particular item (eg Nail, 2x4, plywood sheet, etc). The > key value for that table is the Resource Name. The second table has the > specific project names along with the qty of the materials from table 1. The > main form for this table allows me to create a project, select building > materials from a predefined list, and add the quantities and everything > inputs into the table correctly there. The Key value for that table is > Project. I can't figure out if the next step is another form or query. I > want to type in or select "Bird House" and have Access show the following: > > Bird House > Nails x8 $0.40 > Plywood x1 $1.20 > Varnish x1 $3.50 > Total Price: $5.10 > > I know if I was using Excel the formula would look something like this: > > =SUM((B2*'Resource List'!B8)+(B3*'Resource List'!B11)+(B4*'Resource > List'!B12)) > > But I have no idea how to get that into an Access.

You need to modify the design of your tables: TblMaterial MaterialID Material (Nail, 2X4, Plywood Sheet, etc) MaterialCost Note in this table you must have a sepearate record for each size of a material just as you buy it. For example, Nail is not good enough; you need a record for each size of nail from Brad to Spike. The same for 2X4, if you buy different lengths, you need a record for each length; 2X4X4, 2X4X8, 2x4X10, 2X4X12, 2X4X16 - you get the idea! Plywood comes in different thicknesses and different sizes - you need a record for each of these. TblProject ProjectID ProjectDescription This is just a list of the projects you might build. You might over time build two bird houses but for this table Bird House is one record. TblProjectSize ProjectSizeID ProjectID ProjectSizeDescription (text field descriping the dimensions) The two bird houses mentioned above become become two records in this table because of the different sizes. You need TblProject so you can search your database for all bird houses and you need TblProjectSize so you can identify each size of birdhouse you could build. Finally, you need a table to record the materials needed for each project size in TblProjectSize: TblProjectMaterial ProjectMaterialID ProjectSizeID MaterialID Quantity You can set this all up either as a as-built project database or as a project plan database. Either way the tables are the same - the difference is in when you enter the data. To get the display (form or report) in your example, you need a query that includes TblProjectMaterial and other appropriate tables above and a couple of calculated fields that determine the costs of each material (Quantity x Cost). If you need help setting up the database, I can help you. I provide help help with Access, Excel and Word applications for a small fee. Contact me. Steve santus@penn.com "Dan" <Dan@discussions.microsoft.com> wrote in message news:D709C36A-749A-4A21-BFD3-678C642E1E6A@microsoft.com... > I'm building a database who's end goal is to calculate prices for > different > projects. > My first table has a list of all the separate materials I'll be using and > the cost for that particular item (eg Nail, 2x4, plywood sheet, etc). The > key value for that table is the Resource Name. The second table has the > specific project names along with the qty of the materials from table 1. > The > main form for this table allows me to create a project, select building > materials from a predefined list, and add the quantities and everything > inputs into the table correctly there. The Key value for that table is > Project. I can't figure out if the next step is another form or query. I > want to type in or select "Bird House" and have Access show the following: > > Bird House > Nails x8 $0.40 > Plywood x1 $1.20 > Varnish x1 $3.50 > Total Price: $5.10 > > I know if I was using Excel the formula would look something like this: > > =SUM((B2*'Resource List'!B8)+(B3*'Resource List'!B11)+(B4*'Resource > List'!B12)) > > But I have no idea how to get that into an Access.

This is only my second attempt at a database and I'm still not tracking. I think it might have to do with the way my main table is laid out. The fields in the table are broken down like this: Project - Key Material 1 Qty 1 Material 2 Qty 2 Material 3 Qty3 Material 4 Qty 4 Material 5 Qty 5 Is this table impractical and if so should I try to break it down? As it it right now, each material is selected from a drop down list taken from table 1. I don't konw where to set up my relationship between the two tables. Each of the materials directly pulls from table 1. "Duane Hookom" wrote: > If I understand correctly, just add both tables to your record source and > join on the primary and foreign fields. You can then create a calculated > column (or expression in a control source) that multiplies the quantity times > the cost. > -- > Duane Hookom > Microsoft Access MVP > > > "Dan" wrote: > > > I'm building a database who's end goal is to calculate prices for different > > projects. > > My first table has a list of all the separate materials I'll be using and > > the cost for that particular item (eg Nail, 2x4, plywood sheet, etc). The > > key value for that table is the Resource Name. The second table has the > > specific project names along with the qty of the materials from table 1. The > > main form for this table allows me to create a project, select building > > materials from a predefined list, and add the quantities and everything > > inputs into the table correctly there. The Key value for that table is > > Project. I can't figure out if the next step is another form or query. I > > want to type in or select "Bird House" and have Access show the following: > > > > Bird House > > Nails x8 $0.40 > > Plywood x1 $1.20 > > Varnish x1 $3.50 > > Total Price: $5.10 > > > > I know if I was using Excel the formula would look something like this: > > > > =SUM((B2*'Resource List'!B8)+(B3*'Resource List'!B11)+(B4*'Resource > > List'!B12)) > > > > But I have no idea how to get that into an Access.

Thanks Guys, I think I have enough information to finish this up. Or at least get further along until I get stuck again. I was definately trying to do too much on one table. "Steve" wrote: > You need to modify the design of your tables: > TblMaterial > MaterialID > Material (Nail, 2X4, Plywood Sheet, etc) > MaterialCost > > Note in this table you must have a sepearate record for each size of a > material just as you buy it. For example, Nail is not good enough; you need > a record for each size of nail from Brad to Spike. The same for 2X4, if you > buy different lengths, you need a record for each length; 2X4X4, 2X4X8, > 2x4X10, 2X4X12, 2X4X16 - you get the idea! Plywood comes in different > thicknesses and different sizes - you need a record for each of these. > > TblProject > ProjectID > ProjectDescription > > This is just a list of the projects you might build. You might over time > build two bird houses but for this table Bird House is one record. > > TblProjectSize > ProjectSizeID > ProjectID > ProjectSizeDescription (text field descriping the dimensions) > > The two bird houses mentioned above become become two records in this table > because of the different sizes. You need TblProject so you can search your > database for all bird houses and you need TblProjectSize so you can identify > each size of birdhouse you could build. > > Finally, you need a table to record the materials needed for each project > size in TblProjectSize: > > TblProjectMaterial > ProjectMaterialID > ProjectSizeID > MaterialID > Quantity > > You can set this all up either as a as-built project database or as a > project plan database. Either way the tables are the same - the difference > is in when you enter the data. > > To get the display (form or report) in your example, you need a query that > includes TblProjectMaterial and other appropriate tables above and a couple > of calculated fields that determine the costs of each material (Quantity x > Cost). > > If you need help setting up the database, I can help you. I provide help > help with Access, Excel and Word applications for a small fee. Contact me. > > Steve > santus@penn.com > > > "Dan" <Dan@discussions.microsoft.com> wrote in message > news:D709C36A-749A-4A21-BFD3-678C642E1E6A@microsoft.com... > > I'm building a database who's end goal is to calculate prices for > > different > > projects. > > My first table has a list of all the separate materials I'll be using and > > the cost for that particular item (eg Nail, 2x4, plywood sheet, etc). The > > key value for that table is the Resource Name. The second table has the > > specific project names along with the qty of the materials from table 1. > > The > > main form for this table allows me to create a project, select building > > materials from a predefined list, and add the quantities and everything > > inputs into the table correctly there. The Key value for that table is > > Project. I can't figure out if the next step is another form or query. I > > want to type in or select "Bird House" and have Access show the following: > > > > Bird House > > Nails x8 $0.40 > > Plywood x1 $1.20 > > Varnish x1 $3.50 > > Total Price: $5.10 > > > > I know if I was using Excel the formula would look something like this: > > > > =SUM((B2*'Resource List'!B8)+(B3*'Resource List'!B11)+(B4*'Resource > > List'!B12)) > > > > But I have no idea how to get that into an Access. > > > . >

"Steve" <notmyemail@address.com> wrote in message news:eGPERAKzKHA.6140@TK2MSFTNGP05.phx.gbl... > > If you need help setting up the database, I can help you. I provide help > help with Access, Excel and Word applications for a small fee. Contact me. > > Steve Stevie is our own personal pet troll who is the only one who does not understand the concept of FREE peer to peer support! He offers questionable results at unreasonable prices. These newsgroups are provided by Microsoft for FREE peer to peer support. There are many highly qualified individuals who gladly help for free. Stevie is not one of them, but he is the only one who just does not get the idea of "FREE" support. He offers questionable results at unreasonable prices. If he was any good, the "thousands" of people he claims to have helped would be flooding him with work, but there appears to be a continuous drought and he needs to constantly grovel for work. Please do not feed the trolls. John... Visio MVP

"Dan" <Dan@discussions.microsoft.com> 写入消息 news:D709C36A-749A-4A21-BFD3-678C642E1E6A@microsoft.com... > I'm building a database who's end goal is to calculate prices for > different > projects. > My first table has a list of all the separate materials I'll be using and > the cost for that particular item (eg Nail, 2x4, plywood sheet, etc). The > key value for that table is the Resource Name. The second table has the > specific project names along with the qty of the materials from table 1. > The > main form for this table allows me to create a project, select building > materials from a predefined list, and add the quantities and everything > inputs into the table correctly there. The Key value for that table is > Project. I can't figure out if the next step is another form or query. I > want to type in or select "Bird House" and have Access show the following: > > Bird House > Nails x8 $0.40 > Plywood x1 $1.20 > Varnish x1 $3.50 > Total Price: $5.10 > > I know if I was using Excel the formula would look something like this: > > =SUM((B2*'Resource List'!B8)+(B3*'Resource List'!B11)+(B4*'Resource > List'!B12)) > > But I have no idea how to get that into an Access. > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 4980 (20100328) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 4980 (20100328) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com

Hyperlink field that does not link to the file

113

Utf

Property Sheet units

153

Grip

excel crashing

127

Utf

using tab in form

169

Utf

Access 2003 absence tracker

531

Utf

Consultation by network

Telesphore

hey

Modal form opens on startup - can't get past it. Help?

270

Uncle

Please return my E mail for me

NORMA

Input Masks - Leading Zeros

456

Utf

9/7/2012 8:16:50 PM