dropbox might be changing relationships

[Gûlghâshbúrz]

Hi all,

I've been having a problem with my new database. It consists of 3 tables, one with field name "Members", one with "Products", and one that has those two plus "Date Opened" and "Catalogue Number". It should be a one-to-many relationship from table "Members" to table "Product Opened" and another one-to-many relationship from table "Product" to "Product Opened".


I created a form which the user can put information on table "Product Opened". However, for "Members" and "Products", the user can select from a drop-box field that has the options from the respective tables. So far so good and I was expecting to go smooth. The information that is saved in the “Product Opened” table then saves catalogue number and date opened; products and members are saved as the respective primary keys (even though on the drop-box the options come with full name).

The problem is when I run a query. The query asks the user what kind of product he/she wants to check, but it is not possible to put the name of the products. Also if I add on design view of the query any other table the result (even without asking the user which product) comes as empty.
So far I can’t figure it out what is the problem. I’m wondering if the drop-box is creating some kind of conflict on the relationship.

If anyone can help me to understand this problem, I’ll appreciate a lot!
Cheers.

[orange]

Please post a picture of your tables and relationships.

[Gûlghâshbúrz]

Thanks for the answer. Here are the relationships and a picture of one of the tables.

[orange]

What is the purpose of this database?
Tell us what these things mean

what kind of product he/she wants to check

"Product Opened"

If we are to help you, you'll have to put your "business/opportunity/problem" into context. We know nothing of your application. Only what you tell us; so please give us some more info.



How about
--I work for a company that does ....
--we have Products (such as....)
-- our clients/customers need to .....
--.....

[Gûlghâshbúrz]

Fair enough. Thanks for the help.
Users work in a neuroscience lab and they need diverse products. The products are cell culture related (media and drugs, mainly). Users are members of the lab. Both groups can have additions. These products may vary between the lots; therefore the users would get lot and catalogue number information of these products in order to check if products are causing some effect on cells. Getting this data eliminates variables.
The output of the database then would be a report that shows for a specific product all the times it was used, and for each time when and who opened it and its respective catalogue and lot numbers. The lab members then can compare this report with whatever external source they want to.
I hope this helps clarifying things a bit. If any other information is required, please let me know.

[orange]

I don't know your environment, but it seems to me (these may be relevant and need confirmation)
-You have a number of people (in your lab)
-people conduct tests
- a test involves cell cultures and Products and has a noted outcome
-products may have a positive, negative or neutral affect on a cell culture (test outcome)
-there can be several types of cell cultures
-there are several product types
-each product can come from 1 or more Lots
-each product can have 1 or more catalogue number
-you want to create an inventory of test outcomes by product and culture types

I think there are more than 3 things involved; and that you have to clarify what exactly you are trying to achieve before getting too deep into Access programming.

[Gûlghâshbúrz]

Things that are relevant to this database:
-there is a number of people in the lab
- there are several product types
- each product can come from more than 1 lot
- each product can have more than 1 catalogue number.

The rest is not important to this database since they are done outside it. The tests results are noted on a different software/way.
The main problem on my database, as far as I'm concerned, is that on the form that stores lot and catalogue number, as well as when the products were opened, the drop-box field that gives Product and Lab Member are storing this information in numbers only. These numbers are related to each Product and Lab Member, since they are their primary keys. I don't understand why the drop-box is not storing the text information (names of the members and products).

[orange]

See if this relates to your problem
http://access.mvps.org/access/lookupfields.htm

[Gûlghâshbúrz]

It does, actually! And thanks for giving me the link, I already looked around and found out that the best way of solving this is actually creating a lookup column in the "Products Opened" table instead of a lookup drop-box in the form that fills this "Products Opened" table. As far as I could understand (from your link and some other places) the drop-box has this problem of storing only the ID values, even though the selection on the form is made through the names.
Now with this way I'm doing now it avoids this problem.
Thanks a lot for the help again!

Hmmm the table works fine, a query where you type the Products names works fine, but creating a form with combo-box that allows the user to choose the type of product they want in a report doesn't. I understand that the combo-box is again only getting the Product ID. Is there a way to go around this problem? Many thanks!

EDIT: I got it working just fine now. All I had to do for this last bit is putting the [Form]![Product]![Combo4] on ProductID instead of the name. If the combo-box gives me the ID number, I better use it, huh? =)

Thanks orange for all the help!