adding TWO Queries to a report

[chubbychap]

Hi

I have two separate un-related fields (Field A & Field B) in my main table, each of which will have a "Y" or "N" entered for each record. The contents of Field B are NOT dependant on the contents of Field A. I want to create a query, and then a single report that will show the number of "Y"s present in each field. Something like:

Y's in Field A = Number1 Y's in Field B = Number2.

I have previously created 2 separate queries to calculate the number of Y's in each field. I have found that the only way to get the two numbers to appear on one place is to run another query that basically puts to two numbers together on the same query. I have then run a report on this final query. The way it is currently, it will work, providng that there are Y's present in each Field. If Field A has Y's but Field B has none, my report doesn't show anything for with field. I'm at a loss.

Can anyone help? By the way its my birthday today!!!! If you can help, then you WON'T have to buy me a present

Many Thanks

Chubbychap

 

[Russell Hauf]

For this example, I've used the following:

• Table Name: tbl071003
  Field A: fld1
  Field B: fld2

SQL:

• SELECT A.cnt1, B.cnt2
  FROM
  (SELECT Count(*) AS cnt1 FROM tbl071003
  WHERE tbl071003.fld1='Y') A,
  (SELECT Count(*) AS cnt2 FROM tbl071003
  WHERE tbl071003.fld2='Y') B;

Note that my Y's and N's are text - you may have to adjust if yours are of type Yes/No.

HTH,

Russell

 

[chubbychap]

Russell

Thanks for your efforts - I'm sure it would work in the hands of the right guy. Unfortunately, I'm the wrong guy.....I'm really no good at SQL. To be honest, I haven't got a clue what to do with the SQL you have given me.

Do you know of any way to do what I want through queries and/or macros? If SQL is the only way, then I'd better do some reading up on the subject first.

Again, thanks for your help.

Chubbychap

 

[Russell Hauf]

Try this:

1. Change the names of my table and fields to your table and fields (in the SQL I posted above)
2. Go to the Queries tab, create a new query (in Design View)
3. Click "Close" when the tables list comes up
4. Now there should be a button on the toolbar (probably in the upper-left corner) that says "SQL". Click on that button
5. Paste in the SQL that has your table and field names on it
6. Click the exclamation mark to run the query

If you want to get the results you desire using only one query, you're going to have to do it like this. Sorry.

HTH,

Russell

 

[mdmilner]

In direct answer to your questions:

"Do you know of any way to do what I want through queries and/or macros? If SQL is the only way, then I'd better do some reading up on the subject first. "

Access has an interface built into it called QBE (Query By Example) that is designed to help you write SQL. I think when you're asking above to use a query, you did not realize that the QBE interface and SQL are effectively the same thing.

What Russell has explained is how to open the old familiar interface, but skip actually using it and just drop in the SQL. You can then change back to QBE and see what it looks like there.

Macros are generally intended to run a series of actions, not a single query.

Nice quick problem solution there Russell.

 

[chubbychap]

Russell & mdmilner

I'm sorry to say that I'm still having problems with this. I will explain further.....
As Russell suggested I copied and pasted the SQL he provided into the SQL screen, and changed the Table and field names accordingly, however, when I try to run it I get the following error:

"Syntax error in FROM clause."

To expand on what I entered, my Table name is "Table3", Field A is "Smoking" and Field B is "Referred to Smoking Cessation". The "Y"s entered are free text and not Yes/No options. I therefore entered the following code:

SELECT A.cnt1, B.cnt2
FROM
(SELECT Count(*) AS cnt1 FROM Table3
WHERE Table3.Smoking='Y') A,
(SELECT Count(*) AS cnt2 FROM Table3
WHERE Table3.Referred to Smoking Cessation='Y') B;

I've tried for a few hours to fiddle with it and play around to try and get it to work, but with no success. Any Ideas??????? If you aren't able to help me further, I would like to thank you both for your time and effort.

Cheers

Chubbychap

 

[mdmilner]

Sorry, without looking I'd assumed that Russell's answer would work.
After looking at your question again, I tried and tested this. Seems to work just fine without the subqueries.

<ul>
SELECT Count(Table3.A) AS CountOfA, Count(Table3.B) AS CountOfB
FROM Table3
WHERE A='Y' or B='Y';
[/list]


Mike

 

[chubbychap]

Mike - You're a gem. It worked a treat.

Thanks very much for your help and persistance with me.

I can see that bit of code being very useful in the future.

Cheers

Chubbychap

 

[Russell Hauf]

Sorry, I don't see how this does not return the same number for CountOfA and CountOfB. Take for example the following table:

fld1	fld2
Y	N
N	N
Y	N
N	N
Y	N
N	Y
Y	N
N	N
Y	N
Y	N
Y	N
Y	N
Y	N
N	N

The query posted by Mike will return 10 for both, as there are 10 records with either the first or the second field having a 'Y'. Is this not the case?

 

[mdmilner]

Russell,

Hold up. Looks like Russel is right and I goofed. I checked a couple test values and when I thought about it, it's pretty obvious why mine gives bad numbers. When it totals column 1 for example, if column 2 has a 'Y' in it, it counts it for column with any value (or none). I used a slightly different test table but I should have caught the miscount even on my data.

Copying Russels example (which I failed to look at)

SELECT A.cnt1, B.cnt2
FROM [SELECT Count(*) AS cnt1 FROM Table3 
WHERE Table3.A='Y']. AS A, [SELECT Count(*) AS cnt2 FROM Table3 
WHERE Table3.B='Y']. AS B;

I'm guessing that the problem is a typo or a fieldname/tablename that you didn't change to match yours. When I ran this, my tablename was Table3 and the fields were A & B.

Guess today I re-learned something. I knew you using subqueries can bet he best way to do things but frequently had worked around it.

The syntax for a SQL query to show the results from two separate tables is:

Basics:

SELECT tbl1.*,tbl2.* FROM tbl1, tbl2;

What Russell's original CORRECT query did, was use a query (subquery) to sorta create a temporary single field, single record table that held the correct value that you wanted...then combine them together.

Because you've only created data for what you wanted to use already, you could very slightly simplify the query by substituting an asterisk for the (A.cnt1, B.cnt2) portion of the main query as below.

SELECT *
FROM [SELECT Count(*) AS cnt1 FROM Table3 
WHERE Table3.A='Y']. AS A, [SELECT Count(*) AS cnt2 FROM Table3 
WHERE Table3.B='Y']. AS B;

Sorry about the lecture. My own knowledge tends to be limited to things I've actually done/use all the time. I've found I learn best when I attempt to clearly explain to somebody else some detail or fact. My intentions by posting at all on MrExcel is to improve my own understanding. My current employer doesn't really pay me to do this kind of work, but someday I'd like to turn it into a job.

And since I don't mind admitting when I was wrong. Russell's code should have worked. Nothing I posted in this message is really a change from it.

Mike