One query with mixed data from more than one table

[PLiNk]

Hello, I have a problem that I hope someone can help me with. I am going to explain very thoroughly, so skimm through if you know what i mean. Right know i am solving this problem by having two subforms. This is not so good since they become difficult to navgiate.

Description.
I have two tables. One containig data on papers and one cotaining data on collections of papers, or "leaflets"
A "paper" record may or may not be attached to a "leaflet" record. "Paper" must be searchable and leaflet must be searchable.
What i want is very simple.
Imagine I am seraching for "LOVE". I want both paper "Love is all around" and the leaflet "A collection of love-songs" to appear in the same query.
So that when i sort ascending on title, the record from the two tables are mixed.
Mind, the paper "love is all around" may or not be attached to the "A collection of love songs" leaflet and both must show in the same query.

Thank you very much in advance.

 

[M8KWR]

So the user is on the form, wiht a text box for them to enter a word to search on. This should be a on the main form, where the two subforms are!!!

Lets call the text box "txtSearch1"

In both the subform, with the queries, you need to put

like "*" [forms]![name of main form]![txtSearch1] & "*"

This will look at the text box on the main form and search for paper and leaflets with what ever has been inputted.

If you have a filter button next to the search text box, you will need to refresh both the subforms to update the changes made.

When you open the main form up to start with it will display all records with each subform

I hope this helps, please let me know if this doesn't or if you require any more assistance.

 

[PLiNk]

This is exactly what i have done, on the spot! But what i want is to have one subform with all the records in, and not two.
Essentially when searching for, say, "love" all recors in both tables cotaining the string "love" are equally interesting. It therefore makes sense to put them in the same subform. To do this I need to put two tables with different post names into the same basic layout in a query so that. [Leafletname] and [papernamne] shares the same column in a query.

Can this be done and how?

 

[lespaul]

Using a Union query won't work?

SELECT TITLE FROM PAPER WHERE DESCRIPTION LIKE "*LOVE*"
UNION
SELECT TITLE FROM LEAFLET WHERE DESCRIPTION LIKE "*LOVE*"
ORDER BY TITLE

Leslie

 

[PLiNk]

Thank you. I
am quite new to this, and with no education Union queries are new to me. It isn't the first time I ask questions that are very simple.
Not anymore. The impeccable logic of union queries are now understood.
I am also writing this whilst quite drunk.

Again, thank you for your help.

I have however another question:

Now that I can combine these two classes of records into one query, I would like to have a button that enables the user to access the leaflet to overlook the contents (papers) of this leaflet. How do I make this button not visible, or at least not enabled when showing a paper record? Beacuse if i add the button like I have today, it will produce an error message if the note is not part of a leaflet.


Thanks.

Jens

 

[PLiNk]

or let me rephrase. When the button is on every record shown, every paper and every leaflet, it will produce an error message when clicked upon if the paper does not belong to a leaflet, otherwise it will function as intended. How do I disable the button if ,say, (Belongs_to_leaflet_number IS NULL)

jens