Help Setting Up Tables

[Celestrial_d]

I might have already gone too far, but before I go any further, I would like
to see if I set up my tables properly and how to make them more efficient. I
have a main table. It consists of ReportNumber(uniqueID), Date, Location,
and Duration. It is used for my data entry form. On my data entry form, I
have a subform and the subform uses a ProductUsed table. That table consists
of the ReportNumber –linked from the main table, Product1, Amount Used1,
Product2, Amount Used2, and so forth. Subform allows 6 products to be
entered on the first page and if you have more you can go to 2nd page of
subform.

Now, the table I have questions about is my Product Table. It has a list of
all the products and the specific information about it. I use it as a pick
list for my subform, but I am not sure how pull the other specific
information from that table and use it in my calculations in my reports.

Any suggestions will be greatly appreciated.

Thanks.

 

[Jeff Boyce]

If I'm understanding your description correctly, you have 'repeating fields'
in your ProductUsed table (Product1, Product2, ..., Product6). This is
usually necessary if you are working in a spreadsheet, but not a good idea
if you are working in a relational database like Access. For instance, if
you decide you need only 4 products, or need to add a single new product,
you'll have to re-do your table, your related forms, reports, queries,
macros and code ... a maintenance nightmare!

Instead, since you have described a one-to-many relationship between the
Report and the ProductUsed, use a table with one row per valid
Report/ProductUsed combination.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Celestrial_d]

Product1, Product2, Product3,......Product6 are just to capture what has been
used. If i need to add products, i add to my Products table. The user goes
to the data entry page and when she has to enter the products used, she uses
the drop down lists(pick list from products table) on the 1-6 fields i have
created. This creates Report#1. Then she saves and adds new record(report#)
and fills in main form and subform again. Each report represents a work
order that has been completed.

I created append queries to pull in product from each of the 6 product fields
and append them to 1 field called Product in another table. This way allows
me to filter and sum. The problem i'm having is each product has catagory,
class, and numbers. Depending on catagory and class i do calculations on the
numbers for my reports based on the table i appended to. I dont use catagory,
class and number in my main form. I just need this information to run my
queries and do my calculations for my reports.

Thanks!

 

[Jeff Boyce]

I don't have any experience coercing spreadsheet-organized data into
reports. Perhaps one of the other newsgroup readers has some direction on
this.

Access can do what you've described, but only by using work-arounds. Access
has great features and functions that can do what you want to do, but only
if your data is in well-normalized relational tables.

Adding ProductUsed fields (1, 2, ...) is NOT the way to set up your data in
a relational database if you want to make the best use of the
features/functions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP