Help with Query

[xcel__]

Hi all

I'm new to Access, I've been using excel for a few years, now I need more
power to my data analysis.

Using Excel 2007 and Access 2010

I've a table in design view field 9 Criteria, I've a search [Please Enter
Name] I type say John which find all record for john. In Field 1 there are
numbers, Question: How can I get the query to find all the same numbers
"Records" in that field that John has. Question2: Also Field 7 has numbers
how could make all records that have a 1 in them bold and maybe the row
colour say Yellow.

Hope I've been clear in my questions.

Thanks in advance.

 

[John W. Vinson]

Hi all

I'm new to Access, I've been using excel for a few years, now I need more
power to my data analysis.

Using Excel 2007 and Access 2010

I've a table in design view field 9 Criteria, I've a search [Please Enter
Name] I type say John which find all record for john. In Field 1 there are
numbers, Question: How can I get the query to find all the same numbers
"Records" in that field that John has. Question2: Also Field 7 has numbers
how could make all records that have a 1 in them bold and maybe the row
colour say Yellow.

Hope I've been clear in my questions.

Thanks in advance.

Please don't post the same question repeatedly in multiple newsgroups. The
volunteers who answer here will usually monitor several groups, and it just
wastes folks' time to answer a question that's already been answered in
another group.

Note also that Microsoft no longer supports these newsgroups at all; they
propagated to other servers despite Microsoft's efforts to kill them. See my
..sig for other ways to get help on Access.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John Spencer]

I would have to guess that you are using a query and not the table directly.

If you want to apply criteria against multiple fields you can do so
Field: NameField
Criteria: [Please Enter Name]

Field: NumbersField
Criteria: [Enter Number]

That finds all records where the namefield matches the entry and the number
field matches the entry.

If you are asking to find duplicated numbers - John has 2 or more records with
the same number then use criteria like the following
Field: NumbersField
Criteria: In (SELECT [NumbersField] FROM [YourTable] As Temp WHERE
Temp.[NameField] = [YourTable].[NameField] Group By [NumbersField] Having
Count(*)> 2)

You can use the duplicated query wizard to build a query like this.

Question 2: You cannot do this in a query. You can do this in a continuous
form (or a report) that uses your query as a record source. It requires you
to use conditional formatting on the control in the form to handle the bolding
and the row color. In a report, you can use conditional formatting OR VBA
code in the report section's format event.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dave2010]

My apologies John W. Vinson.

I posted in Outlook Express first, it did'nt show up so I posted from
google groups.
Now it shows 1 message in Outlook Express, this one and 2 in Googe. It
gives me no option to delete the first messege in Google.




Hi John Spencer

I ran your code in the query but it was running for 20 minutes, maybe
some not right, so I aborted.

Do I put all this code in the criteria cell field1?........... on the
same line as [Please Enter Horse] HORSE NAMES (Field Name)

In (SELECT [Field1] FROM [MAIN JUST THE RACES 2010 (5)] As Temp WHERE
Temp.[HORSE NAMES] = [MAIN JUST THE RACES 2010 (5)].[HORSE NAMES]
Group By [Field1] Having Count(*)> 2)

It's actually Horses names. Should of said that in the first post.

I'm trying to get all the horses that ran in the same races as [Please
Enter Horse]

All Races have a Number in field1.

Thanks for your help John.

 

[John Spencer]

If I understand what you want then you can try a query like the following.

SELECT [Horse Names], [Field1]
FROM [MAIN JUST THE RACES 2010 (5)]
WHERE [Field1] in
(SELECT [Field1] FROM [MAIN JUST THE RACES 2010 (5)] as TEMP WHERE TEMP [Horse
Names] = [Please Enter Name])

If you want each horse listed only once, then you can change the query slightly to
SELECT DISTINCT [Horse Names]
FROM [MAIN JUST THE RACES 2010 (5)]
WHERE [Field1] in
(SELECT [Field1] FROM [MAIN JUST THE RACES 2010 (5)] as TEMP WHERE TEMP [Horse
Names] = [Please Enter Name])

In query design view:
== Add field horse names and field1
== Criteria under field 1 would be
in (SELECT [Field1] FROM [MAIN JUST THE RACES 2010 (5)] as TEMP WHERE TEMP
[Horse Names] = [Please Enter Name])

Uncheck SHOW for field 1 and set the unique values property of the query to
yes if you want an unduplicated list of horses.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dave2010]

Hi John

I tried the first piece of code by pasted each line of your code to
the criteria: of Field1, it came up with an error went I tried to save
it.

The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses.

In the HORSE NAMES field criteria: I have [Please Enter Horse]

Still new to access I've only been working with it for the last month.

Thanks John

Dave

 

[John Spencer]

IF I understand what you want, then the query I posted was the entire query
(SQL view).

If you are working in the query design view (a grid) then remove the criteria
from under Horse Names and under Field1 enter the following as criteria.
in (SELECT [Field1] FROM [MAIN JUST THE RACES 2010 (5)] as TEMP WHERE TEMP
[Horse Names] = [Please Enter Name])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dave2010]

Hi John

I deleted all criteria, and pasted your code to my Query in design
view and it came up with an error. Tried the code in Field1 and Horse
Names, as you did not mention where to put the code.

Syntax error in query expression '((([MAIN JUST THE RACES 2010 (5)].
[HORSE NAMES]) In (SELECT [Field1] FROM [MAIN JUST THE RACES 2010 (5)]
as TEMP WHERE TEMP [Horse Names] = [Please Enter Name])))'.


Thanks

Dave

 

[John Spencer]

Ok. It took me a bit to see it but the sub-query in the criteria was missing
a period between TEMP and [Horse Names].

I hate it when the computer doesn't understand what I meant!!! On the other
hand, they don't tend to misinterpret me on purpose as some people do.

In the criteria under Horse Names, you should see just the following.
In (SELECT [Field1] FROM [MAIN JUST THE RACES 2010 (5)] as TEMP WHERE
TEMP.[Horse Names] = [Please Enter Name])

Hopefully, I have no other syntax or typing errors in that.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dave2010]

Hi John

LOL I just love computers.

I pasted the code.

In (SELECT [Field1] FROM [MAIN JUST THE RACES 2010 (5)] as TEMP WHERE
TEMP.[Horse Names] = [Please Enter Name])


It saves the query, when I run the query I get a popup saying:

Type mismatch in expression.

I clicked on help and it says:

Type mismatch in JOIN expression. (Error 3615)
A JOIN expression is attempting to join two tables on fields of
incompatible data types. For example, you will get this error if you
attempt to join a Memo field with a Text field.


Thanks

 

[John Spencer]

As a guess, your Horse Names field is a lookup field or a multi-value field.

If it is lookup field, then the value displayed (a text string) is not the
value that is actually stored (probably a number). If it is a multi-value
field then you will have to find another helper. I avoid the multi-value field.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Dave2010]

Hi John

No problem at all.

Thanks very much for all of your help.

All the very best.

Dave

 

[John Spencer]

I suggest you start another thread, as this one is deep enough that it may
well be ignored.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County