query yeilding no records

[Idjit]

Hi, I'm new to the bb and I really hope someone can help me. My problem is similar to paulmconnel's posting from yesterday, but I don't know SQL so the responses didn't help me.

I have multiple queries set up, each to pull information out of a different table (the total number of interviews conducted during a given month). I want to design a query that will pull all of this info together in one record, i.e., during November, 10 people completed intake, 4 completed the first interview, 3 completed the second, etc.

My problem is that when this master query is run, no records are produced. When I include only one query in the design it works fine, but as soon as I add a second query there's no data. I thought that maybe the problem is that there are no joins between the separate queries, but there aren't any fields on which to define a relationship (in the tables, data is stored by client, with one record per person, and separate variables indicating date of interview and outcome).

Sorry this message is so long, but I wanted to make sure I included all the info I could.

 

[Fuga]

Do you think you could be a bit more clear on your table structure? It sounds to me that there could be a problem there.

Fuga.

 

[Idjit]

Sorry, I think I mistakenly described the structure of a different query. Actually, all of the information I need is in one main table. Each record contains all of the information for one person. There are separate fields to record the date completed and status of each interview (e.g., int1date contains the date interview 1 was completed and int1stat contains the status (completed vs. refused), int2date the date of interview 2, etc.).

In order to get the information I need, I created separate queries - one query that yields a count of completed interview 1s for a certain date range, one that yields the number of people who refused interview 1 during that same period, and so on for each of the interviews. These work fine alone, but I want to pull all of this info into one query to be used in a report. When I do this, the master query yields no records. As I said in my first post, I thought this might be because the individual queries aren't joined, but since each query produces a count of a different field, I don't know how I could link the tables.

I hope this makes more sense.

 

[David R]

Because of your structure it is going to be difficult to get what you ask. Your data is not properly structured (for Access) so the query engine is throwing a rod.

You can probably get what you want from a UNION query temporarily, but in the long run I highly suggest you look into 'database normalization', either on the web, in a book, or in the archives here or at the MS Knowledge Base.

UNION queries are written in SQL however; you cannot do them in Design View. Here is an example to help you get started:

SELECT [Arr1] AS [Arr], [DateField]
FROM YourTable
UNION 
SELECT [Arr2] AS [Arr], [DateField]
FROM YourTable
UNION
SELECT [Arr3] AS [Arr], [DateField]
FROM YourTable
UNION
...
UNION
SELECT [Arr9] AS [Arr], [DateField]
FROM YourTable

 

[kavarin]

I will admit upfront that I am a scrub at Access as well, but from the description you gave, it sounds as though the reason that your "master" query is not showing records is because the queries are processed in order, therefore, if your first query is to pull records that you other queries would not pull..it would eliminate that record form you query because the criteria of the fields are conflicting with each other./
Example:
You said that your first query shows all completed first interviews, and your second query shows all 1st interviews that were refused (in essence, they were NOT completed). You then run a single query that joins both of these smaller queries together. You are basically asking the syste, "Show me all completed 1st interviews that are not completed"
As a result you have no matching records.

Am I in line with your process on this?

 

[Idjit]

Thanks for the quick replies, everyone. I've been playing around with the db, and I think I've found a way to McGuyver what I need.

If anyone's interested, the way I ended up doing it was to include the primary key in the individual queries, start the master query with the main table, add in each of the individual queries linking by the primary key and specifying that all rows in the main table should be included, and add in the "counted" fields from each query as a "sum" field. I won't win any prizes for parsimony, but at least I seem to get the correct numbers. Although the point is well taken that I need to brush up on basic db design.

 

[kavarin]

WTG!!

Hope it works for you