Problem Joining Data Types in Access 97

singularity2006
10-23-2004, 12:46 AM
I have two tables, a transaction table and a lookup table. I'm having problems getting my query to work between the two. Here's how they look:

Lookup Table:
Chemical Name, Cost Per Pound

Transaction Table:
Customer ID, Date of Treatment, QTY Chemical 1, QTY Chemical 2, QTY Chemical 3 ... etc.

I'm trying to link the names I used in the transaction table with the lookup table but it's totally not working. I was thinking of doing it this way:

Customer ID, Date of Treatment, Chemical ID, Chemical QTY.

The only thing is that on days where multiple chemicals are used, I'd be getting a lot of duplicate entries for date and customer ID and all that. Can anyone offer any other ideas? I gotta use the QTY values and the lookup table to calculate total cost for chemicals used.

Dennis DVR
10-23-2004, 03:17 AM
Is there any relational field between Lookup Table and Transaction Table? I this a 1 to many relationship? If yes, then a left join would do the trick, and about your problem regarding duplicate result of Customer ID in your query, if the Customer ID field reside in the Parent table or has multiple entries in the Child table that have the same value of PK or relational field of Lookup Table and Transaction Table, I don't think you can eliminate the duplicate entries in the result of the query.

singularity2006
10-23-2004, 02:47 PM
Is there any relational field between Lookup Table and Transaction Table? I this a 1 to many relationship? If yes, then a left join would do the trick, and about your problem regarding duplicate result of Customer ID in your query, if the Customer ID field reside in the Parent table or has multiple entries in the Child table that have the same value of PK or relational field of Lookup Table and Transaction Table, I don't think you can eliminate the duplicate entries in the result of the query.

Yeah, this is a 1 to many relationship. But in our class, we have only been dealing with 1 to 1 relationships only. The teacher hasn't been giving us very much technical lecture. How does a 1 to many relationship work?

Using the original table, I tried linking all the "QTY Chemical #" from the transaction table to "Chemical Name" in the lookup table and it gave me a 5 to 1 link. It gave me a join error. Doing it the other way from the "Chemical NAme" to "QTY Chemical #," I also got a join error ...

Dennis DVR
10-24-2004, 10:36 AM
Yeah, this is a 1 to many relationship. But in our class, we have only been dealing with 1 to 1 relationships only. The teacher hasn't been giving us very much technical lecture. How does a 1 to many relationship work?

read this link http://techrepublic.com.com/5100-6270_11-5285168.html
i has 3 pages, so read the whole topic.


Using the original table, I tried linking all the "QTY Chemical #" from the transaction table to "Chemical Name" in the lookup table and it gave me a 5 to 1 link. It gave me a join error. Doing it the other way from the "Chemical NAme" to "QTY Chemical #," I also got a join error ...

what is the relation or common field between Lookup Table and Transaction Table? you need to provide more information to be able for us to help you.

singularity2006
10-24-2004, 01:36 PM
yikes... now I'm feeling more confused than ever. I'm lacking in the technical aspect of this class, really. Anyhow, here's a picture of what my database looks like so far:

http://img.photobucket.com/albums/v192/singularity2006/relationships.jpg

Anyway, I'm lost trying to explain myself so here's the goal:

Table: Treatments
This is the table that is giving me the hardest time. We're required to let a user input amounts in various chemical types. The hard part is that I don't know how to relate the treatments table to the "chemicals" table in order to calculate total cost per chemical. Because in "QTY [Chemical]," the data type is number whereas in CostPerPound, the data type is currency....

Any further help would be GREATLY appreciated....

singularity2006
10-24-2004, 04:28 PM
While going through some other online resources I google'd up, I came across a good one here:

http://www.miswebdesign.com/resources/articles/wrox-beginning-php-4-chapter-3-2.html

Based on working with that model, I came to use my secondary method of organizing my transaction table:

Job ID, Customer ID, Treatment Date, Outside Treatment Only?, Chemical Name, Chemical Quantity.

In the instances where a customer wants multiple types of chemicals used during the same treatment date, I end up having to simply create a new record for each chemical used while using the same info for the date and customer ID. This is perhaps more scalable but does not seem like the best method. However, it is much easier to work with the data than doing it:

customer ID, chemical 1 qty, chemical 2 qty, etc...

Any suggestions/comments about this way of building the database?

Dennis DVR
10-25-2004, 07:04 AM
Reading at loquin ... would really help and give you a better idea about normalize database structure and naming fields.

singularity2006
10-25-2004, 09:47 AM
Yeap, most definitely. Got the table working nicely... the hard part is those queries. Here's another question though.... after generating my report based on filtered query results, why is it that when I close the report and re-open it, the results are no longer filtered? How do I force the report to always display filtered results?

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum