Main, Junction Query (I have been trying for two days)

[floyd]

My main table is tblMainFile
I have junctions (listed) which leads to (listed)

1. tblJncFileDelivery ---> tblDatDelivery (6 rows of choices - Can have multiple choices as a file can be delivered different ways)
2. tblJncFileAudience--->tblDatAudience (10 rows of choices - Can have multiple choices as a file can be delivered to different audiences)
3. tblJncFileTopic------>tblDatTopic (30 rows of choices - Can have multiple choices as a file can have multiple topics)

The tblMainFile is tied to the various junctions with a PK.

I am trying to create a query which will allow the user to Select (from a combobox) data from tblDatDelivery (and/or) data from tblDatAudience (and/or) data from tblDatTopic

The challenge is, when I create the query, I am getting duplicate records unless I fill in all three of the comboboxes!

The reason is, the item from tblMainFile can have ONE link to tblDatDelivery but MULTIPLE links to tblDatAudience and tblDatTopic (and vice versa)

I'm thinking I have to do three queries (one for each pull down box) and then somehow pull the data together when I click a button, but I can't figure out how to make this happen!



Any help would be GREATLY APPRECIATED!

Joe

[floyd]

Is there more information that I can provide which would help answering my question?

[ssanfu]

I don't know if I can help you, but.....

It would help to see:
what your table structures are; what fields are in which tables.
example data from each table...

I'm thinking I have to do three queries (one for each pull down box) and then somehow pull the data together when I click a button, but I can't figure out how to make this happen!

From your description, I would have 3 sub forms (maybe on tabs). There could be a combo box on the subform to make the selections.

Maybe post your dB? (delete/change any sensitive data)

[floyd]

Here is the current structure.
Let me know if you need anything else.

[floyd]

Is there anything else I can offer so someone can help me?
I'm about to give up but I know this is possible.

[June7]

Bizarre data structure. Don't understand it at all and I have tried to in your other threads.
Maybe this is relevant: http://www.codeproject.com/Articles/...atabase-Design

[ssanfu]

I am trying to create a query which will allow the user to Select (from a combobox) data from tblDatDelivery (and/or) data from tblDatAudience (and/or) data from tblDatTopic

I am not sure what you are trying to do with the "query".

I have to agree with June7..... this is a very strange structure.

But... is the attached mdb close to what you are trying to do?
There are two different "Main" forms: "MainJunction" that has a tab control with subforms and "MainJunction2" that has subforms in the footer.

[floyd]

After some considerations, I made changes to the database.
I think this makes more sense but my original question still holds true on what I am trying to accomplish.
Here is the new structure and the new database.
teamtoolbox1.zip

When you open this up... you can see that I have a few forms for inputting information.
frmInputCourse
frmInputFiles

What I am looking for are two forms which allows the user to do the following:

Form 1 (Course Search) - Using three combo boxes, (Audience / Delivery / Topic) Allow the user to find the course(s) associated with any, or all of the three combo box choices.
Form 2 (File Search) - Using three combo boxes, (Audience / Delivery / Topic) Allow the user to find the file(s) associated with any, or all of the three combo box choices.

[June7]

Each MainCourse can have multiple Delivery, multiple Email, multiple Topic, multiple Audience?

Each Delivery, Email, Topic, Audience can associate with more than one MainCourse?

[Dal Jeanis]

To answer your main question, don't put the query together into a single query. When a person fills in one or more dropdownboxes, have the results displayed in three different listboxes - one for available delivery methods, one for available audiences, one for included topics.

They will all be based upon a single query that includes all the selected limitations, but each listbox will only display the single characteristic for that list box, based on a GROUP BY on that characteristic.

Query1:
SELECT Course, Delivery, Audience, Subject
FROM Whatever
WHERE Whatever;

QueryA:
SELECT Audience
FROM Query1
GROUP BY Audience;

QueryD:
SELECT Delivery
FROM Query1
GROUP BY Delivery;

QueryS:
SELECT Subject
FROM Query1
GROUP BY Subject;

[floyd]

Each MainCourse can have multiple Delivery, multiple Email, multiple Topic, multiple Audience?

Each Delivery, Email, Topic, Audience can associate with more than one MainCourse?

#1 - Correct - example: A course could be taught in a classroom setting or over Adobe connect. It can also cover multiple topics like - Sales & Forecasting
#2 - Actually.... yes and no. A file either associates with one class, or lives by itself as a single piece of usable material.

I found that using the following I am able to get close:

(LIKE "*" &) in the query.
The problem is... when I choose the first field (Topic) from the combo box... I get multiples of the associated records
When I choose the second combo box field, it defines that a bit more
When I choose the final field combo box it defines it to the file

I need people to be able to choose one or all of the combo boxes and ensure they only receive ONE of each record.

[Dal Jeanis]

Supposing that they select settings from the combo boxes that can result in twelve different possible valid combinations. Do you care which one the user receives?

[ssanfu]

Form 1 (Course Search) - Using three combo boxes, (Audience / Delivery / Topic) Allow the user to find the course(s) associated with any, or all of the three combo box choices.

See Attached MDB. I only set up a form for searching for courses. The form for files would be similar.

The search form is not up-datable because it is based on a union query.

Is this closer???

[floyd]

ssanfu... this is close but it is working backwards.

If I do a query by a topic.
It should pull up each class that has that topic. (BUT only one version of the file based on the file name)

If I ADD the audience query
It should filter and pull up each class that has the topic AND also the corresponding audience. (BUT only one version of the file based on the file name)

If I ADD the Delivery query
It should filter and pull up each class that has the topic, audience AND the corresponding delivery. (BUT only one version of the file based on the file name)

The problem is... duplicate records in the query.
I do not want to get rid of the "duplicate entries", but I do want to remove them from the query results.

What can I do in a query to limit the results to one record per unique field of my choosing?

Thanks in advance for any help.

[ssanfu]

OK...
Try this version.



I made quite a bit of changes. I couldn't get the results you wanted with the structure you had. (Plus I am working without your data and examples)
Look at the Relationship window.

The form "frmFindcourses2" is to see/verify what courses are found. The form "frmResults" are what I think you want...??