Querying Help - displaying one result for records with same value

[Tom Keane]

Okay, woo, yet another issue I have. I remember writing about this issue
AGES ago, but I don't think it worked, or I just left it for too long.

I have a query that searches for specific records according to the invoice
number. It will display information from two tables, Customer Details and
Invoices. The result is sorted in terms of a field called JobNo. So to make
it simple, if I wanted a query to display all the entries for invoice number
4227, I would get a result similar to the following:

JobNo NAME
100 Jimmy Buffet
100 Pablo Picasso
100 Harry Potter
101 Seth Green
101 Pam Anderson
102 Gary Oldman
What I want in my query is to only display one result per job number, so
when I get the result for the query, it would appear like this:

JobNo NAME
100 Jimmy Buffet
101 Seth Green
102 Gary Oldman

It doesn't matter which name I get, it is the Job number and the other
details in my query that are important.
Any help?

Thank you,
Tom Keane

[jimfortune]

Tom Keane wrote:

Okay, woo, yet another issue I have. I remember writing about this issue AGES ago, but I don't think it worked, or I just left it for too long.
I have a query that searches for specific records according to the invoice number. It will display information from two tables, Customer Details and Invoices. The result is sorted in terms of a field called JobNo. So to make it simple, if I wanted a query to display all the entries for invoice number 4227, I would get a result similar to the following:

JobNo NAME
100 Jimmy Buffet
100 Pablo Picasso
100 Harry Potter
101 Seth Green
101 Pam Anderson
102 Gary Oldman
What I want in my query is to only display one result per job number, so when I get the result for the query, it would appear like this:

JobNo NAME
100 Jimmy Buffet
101 Seth Green
102 Gary Oldman

It doesn't matter which name I get, it is the Job number and the other details in my query that are important.
Any help?

Thank you,
Tom Keane

Note that Name is a reserved word in Access so naming a Field 'Name' is
not a good idea. If qryJobsByInvoice is your query that displays:

JobNo theName DataField
100 Jimmy Buffet BuffetData
100 Pablo Picasso PicassoData
100 Harry Potter PotterData
101 Seth Green GreenData
101 Pam Anderson AndersonData
102 Gary Oldman OldmanData

then

SELECT JobNo, First(theName) AS ContactName, First(DataField) AS Data
FROM qryJobsByInvoice GROUP BY JobNo;
should give:

JobNo ContactName Data
100 Jimmy Buffet BuffetData
101 Seth Green GreenData
102 Gary Oldman OldmanData

James A. Fortune

[Tom Keane]

> SELECT JobNo, First(theName) AS ContactName, First(DataField) AS Data

FROM qryJobsByInvoice GROUP BY JobNo;
should give:

JobNo ContactName Data
100 Jimmy Buffet BuffetData
101 Seth Green GreenData
102 Gary Oldman OldmanData

When I do this, an error message comes up saying

"You tried to execute a query that does not include the specified expression
'JobNo' as part of an aggregate function"

It pretty much doesn't let me do anything unless I have the First()
expression around it. But if I do that it only displays the very first
record, not the first record for each job number.

What can I do to fix this?

Thank you for your help,
Tom

[jimfortune]

Tom Keane wrote:

SELECT JobNo, First(theName) AS ContactName, First(DataField) AS Data FROM qryJobsByInvoice GROUP BY JobNo;
should give:

JobNo ContactName Data
100 Jimmy Buffet BuffetData
101 Seth Green GreenData
102 Gary Oldman OldmanData

When I do this, an error message comes up saying

"You tried to execute a query that does not include the specified

expression 'JobNo' as part of an aggregate function"

It pretty much doesn't let me do anything unless I have the First()
expression around it. But if I do that it only displays the very first record, not the first record for each job number.

What can I do to fix this?

Thank you for your help,
Tom

You need to change the Name field to theName in both the table and the
query. If that's not possible just change it in the query using QBE
like: theName: Name. Also, your query must include the name of the
data field that you want to include instead of just the two fields you
show. Replace 'DataField' with the actual name of the field from the
table in qryJobsByInvoice and in the new query. That should fix the
problem. If not, post back with the SQL you use for qryJobsByInvoice
and I'll try to show you what the final query should look like.

James A. Fortune

[Tom Keane]

> You need to change the Name field to theName in both the table and the

query. If that's not possible just change it in the query using QBE
like: theName: Name. Also, your query must include the name of the
data field that you want to include instead of just the two fields you
show. Replace 'DataField' with the actual name of the field from the
table in qryJobsByInvoice and in the new query. That should fix the
problem. If not, post back with the SQL you use for qryJobsByInvoice
and I'll try to show you what the final query should look like.

It works now. Thanks ;)