Help w/Query

[Celestrial_d via AccessMonster.com]

I am trying to build a query based on 2 tables. Main Table has Entry#, Date,
Location, Product1, Amount1. Product Table has ProductID, Content, and
Catagory. I am trying to add the Content and Catagory for the product that
is selected in the main table. Because later on i need to sum up the amount
of product in each catagory.

So far i have an append query with the tables i need. I have a join between
product1 and productID where all records from main table and only records
from Product Table where those are equal.

When i run the query i get product1 listed for every content and catagory
combination. I only need the content and catagory that matches product.

Any help will be greatly appreciated. Thanks.

 

[John W. Vinson]

I am trying to build a query based on 2 tables. Main Table has Entry#, Date,
Location, Product1, Amount1.

The fieldname makes me a bit queasy here. I hope you DON'T have (or plan)
field Product2 and Amount2, or further? Can you safely assert that an Entry
will NEVER have more than one Product? Because if not... you need another
table (an OrderDetails table, to use the logical parallel to the Northwind
sample database).

Product Table has ProductID, Content, and
Catagory. I am trying to add the Content and Catagory for the product that
is selected in the main table. Because later on i need to sum up the amount
of product in each catagory.

So far i have an append query with the tables i need. I have a join between
product1 and productID where all records from main table and only records
from Product Table where those are equal.

You do NOT need an Append query, unless I'm really missing something. If you
want to pull the Amount from Main Table and the Content and Category from the
Product table, all you need *is a query* joining Main Table to Product Table,
joining Product1 to ProductID. This Query can be made into a Totals query, or
you can base a Form or Report on it, and sum the amount in the form or report
Footer. It is emphatically NOT necessary (nor is it good design) to store the
data from these two tables redundantly in any other table!

John W. Vinson [MVP]

 

[Celestrial_d via AccessMonster.com]

What I have is a Work Order. Information from the Work order is getting
entered into the database. I have Date, Location, Complex, Sandblast
Duration, Paint Duration, Sandblast Amount. I have a subform that has Paint
Used and Amount. Since there can be several paints used on one work order, I
have Paint1, Amount1, Paint2, Amount2......Paint6, Amount6. I have several
append Queries that append all the Paints and amounts to one giant table and
assigns all the paints to one fieldname. This is when i run into problems.
Getting information from the PaintsTable so i can sum and calculate gal/hr
and certain emissions.

When i open my report, it deletes the infomation that is stored in the "giant
table" and runs the append queries to repopulate the table to get information
i need for my reports.

If you have a better idea or solution to my problem, anything will be greatly
appreciated. Thanks..

 

[John W. Vinson]

What I have is a Work Order. Information from the Work order is getting
entered into the database. I have Date, Location, Complex, Sandblast
Duration, Paint Duration, Sandblast Amount. I have a subform that has Paint
Used and Amount. Since there can be several paints used on one work order, I
have Paint1, Amount1, Paint2, Amount2......Paint6, Amount6. I have several
append Queries that append all the Paints and amounts to one giant table and
assigns all the paints to one fieldname. This is when i run into problems.
Getting information from the PaintsTable so i can sum and calculate gal/hr
and certain emissions.

When i open my report, it deletes the infomation that is stored in the "giant
table" and runs the append queries to repopulate the table to get information
i need for my reports.

If you have a better idea or solution to my problem, anything will be greatly
appreciated. Thanks..

Well, yes, as a matter of fact this can be improved markedly.

You're "committing spreadsheet upon a database", a misdemeanor punishable by
being required to restructure your database. A properly normalized database
will NOT have fields named Paint1, Paint2, Paint3... etc.; instead you will
move this one (workorder) to many (paints) relationship *into a new table*.
I'd see:

Workorders
WorkorderID <primary key>
WorkDate <don't use the reserved word Date as a fieldname>
Location <probably a link to a table of locations>
Complex <whatever this is...???>
SandblastDuration
SandblastAmount

Paints
PaintCode <mfgr's ID for the type of paint used>
ColorCode
<other info about the paint as a material>

PaintsUsed
WorkorderID <link to Workorders>
PaintCode <which paint was used on this order>
Amount <how much>
PaintDuration <how long did it take - Long Integer minutes, NOT Date/Time>

If you use three paints on a workorder, you'll put three records into
PaintsUsed; if you use eight paints (on a Victorian Revival painted-lady house
<g>) you'll put eight records (and need to completely redesign your tables,
forms, queries and reports if you stick with the spreadsheet).

You might want to review some of the database design suggestions in:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

particularly Jeff's "Database Design 101" links.

John W. Vinson [MVP]

 

[Celestrial_d via AccessMonster.com]

Thank you very much for your advice! Now I'll shall serve my time reading
the 10 Commandments of Access Database and restructure away.