Need help with this database

[Dan]

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.

 

[Duane Hookom]

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.

 

[Steve]

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
(e-mail address removed)

 

[Dan]

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.

 

[Dan]

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.

 

[John... Visio MVP]

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

 

[Guest]

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

 

[Guest]

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