Query question -- how to avoid using AND

[gkh]

Hello,

I am just learning. I have a query question. I am learning to use OR and AND. I understand that in some cases using AND will result in no records returned when each record is unique.

I want to be able to make a query that will let me focus on two fields. In field #1 I have replicates -- they can be sorted and alphabetical order. In field #2 I have different subjects -- lets say A to Z. I then want to find those records in field #1 that have both B & G -- if I use OR I can get those that have B&G -- but how can I find records that have only B&G?

Thank you,

gkh

 

[Andrew Fergus]

Hello and welcome to Mr Excel.

I think I understand the problem and have copied some SQL below that you can copy into the SQL view of your query. I have assumed that the two fields are a joint primary key such that you can't have repeated instances in both fields.

Try something like this :

SELECT tbl_gkh.field1, Count(tbl_gkh.field2) AS CountOffield2
FROM tbl_gkh
WHERE (((tbl_gkh.field2)="B" Or (tbl_gkh.field2)="G"))
GROUP BY tbl_gkh.field1
HAVING (((Count(tbl_gkh.field2))>1));

I used the table name "tbl_gkh" and the fields names "field1" and "field2". Make sure you use your actual field and table names.

Based on my original assumtion this counts the number of instances of field 2 that equals either B or G and returns field 1 where the count exceeds 1 - in other words, field 1 has both B and G in field 2.

HTH, Andrew.

 

[gkh]

Hello Andrew,

Thank you very much. I had a thought of using numbers such as 1 or 0 or else true or false (T or F), but as I am such a newbie I have never done SQL or Visual Basic so I will give what you suggest my best shot!

I will look in some texts to see where to put in your SQL statement!

Again, I greatly appreciate your help!

gkh

 

[Andrew Fergus]

Hi gkh

In answer to this :

I will look in some texts to see where to put in your SQL statement!

Whe you are in the query design screen, click View -> SQL. You can then copy and paste the SQL but change the table and feild names.
HTH, Andrew.

 

[gkh]

Hello Andrew,

Thank you very much. In my exploration today I did discover the view button where I saw SQL view -- but, you have now helped me with the what do I do now question I had! I will give the proper names and give it a try.

I would like to pose another question. I am not very interested in forms or reports, but I do want to learn to make good queries. Should I investigate learning SQL or VBA to help me? Which is more useful in the long run?

Again, many thanks,

gkh

 

[Andrew Fergus]

In answer to this question :

Should I investigate learning SQL or VBA to help me? Which is more useful in the long run?

You don't need to learn SQL to write good queries. Most queries can be developed using the standard Access query design screen. Whilst the SQL is the query 'coding', it is also a useful way of presenting queries on forums such as this. I personally don't write the queries in SQL, I merely present them that way to eliminate confusion - it takes too long to describe them

Regarding VB : I couldn't write a VB script to save myself (I never bothered to learn it - maybe I should) and can't comment on that. It's probably a question of personal preference. There are some very good VB coders on this forum who would probably prefer VB. There are also some good VB help sites that you will find through Google, although VBA Express seems to be popular with some of the Mr Excel contributors.

Back to query designs, there are some useful resources here from MS and here plus Mr Excel, of course! However, keep in mind that writing 'good' queries is also a matter of having a well designed database - there is webpage here on database normalisation.

Lastly, don't discount the forms and reports too quickly, IMO they are very handy features.

Andrew