Query the Same Table Twice

[MrSquigglesworth]

I am making a candy database for a class, and I have one table that contains the composition of each candy next to the candy_ID and the corresponding composition_ID. For something like snickers, there will be duplicate candy_IDs for each composition_ID. So if snickers was candy_ID #37, it would be peanut; #37 & in the next row, nougat; #37. The trouble when I link everything together in access is that I can only search for one composition at a time. I can find all the candy that has nougat or peanut. If I put two compositions side-by-side in a search for "peanut" and "nougat" it gives me nothing. If I put "peanut" below "nougat" in a composition search column, it gives me "nougat" or "peanut." Is there a way I can search both? Perhaps it is flawed how I have organized the raw data.

[orange]

There is a free data model here
http://www.databaseanswers.org/data_...ipes/index.htm

[MrSquigglesworth]

Thank you, but I still need to figure out how to reformat my tables to make this work.

[orange]

I don't have acc2010 so can not open accdb files.

Can you post a picture of your tables and relationships?

[MrSquigglesworth]

Sorry I am struggling to fit pictures of the right size in this size limit.

[MrSquigglesworth]

[MrSquigglesworth]

Ok, apparently I can't post pictures in here.
One table would be:
Composition_ID;Composition;Candy_ID
1 Chocolate 1
2 Caramel 1
3 Rice 1
and the main other table would be:
Name ; Candy_ID
100 Grand Bar 1

The two would be linked by Candy_ID, and the tables are much longer, so other things contain rice, caramel, and chocolate, but this is the only one that contains all three (or possibly the only one that has rice and caramel).

[orange]

You might want to look at this
http://support.microsoft.com/kb/283878

[MrSquigglesworth]

Still can't figure it out. I am getting Expr1000 when I try to access a table for two things. I have read other forums where this has occured http://help.lockergnome.com/office2/...pict90840.html and http://www.ozgrid.com/forum/showthre...t=52032&page=1, but with no solution that works. Anyone know of any work arounds?

[orange]

By default, when you create a query via the Query Wizard that involves calculations, M$oft will assign a variable name like Expr1000.

If you could show the query, I'm sure someone could help.