Distinguish between same field names in 2 tables

[Susan May]

I have 2 tables that have FirstName and LastName in each table. I am making
an expression to combine the First and last name together, but Access doesn't
know which table I'm referring to. I've done this before but can't seem to
get the sytax right.

RDName: [FirstName] &" "& [LastName] is my expression. The table name is
RD. How and where do I insert RD in this expression so the query will run.
I've tried
RDName: [RD].[FirstName] &" "& [RD].[LastName] and it doesn't work. I've
also tried it with [RD]![FirstName] &" "& [RD]![LastName] and that doesn't
work either.

Can somebody get me straight here?

Much appreciated.

 

[Michel Walsh]

With Jet, you should be able to use the syntax tableName.FieldName or if
table1 and table2 have both a field name f1, then from the query:

SELECT table1.*, table2.*
FROM table1, table2


saved as myQuery, then:


SELECT myQuery.table1.f1
FROM myQuery


can be used. The same syntaxt DOES NOT WORK for MS SQL Server, since the
syntax: dbname.schema.table.field is understood, in the multiple dot
context.


A better solution can be to NOT use * and alias the field differentlÿ, for
myQuery:


SELECT table1.f1, table2.f1 AS f1FromT2
FROM table1, table2



and then, there is no more collision for the columns names.


Vanderghast, Access MVP

 

[Duane Hookom]

This should work in a query:
RDName: [RD].[FirstName] &" "& [RD].[LastName]
What do you mean by "doesn't work"?

How about providing the complete SQL view of the query that "doesn't work"?

 

[Jeff Boyce]

Susan

Why do you have two tables with identical fields? Would your table
structure benefit from further normalization?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Susan May]

Hi Duane:

I couldn't follow what Michel Walsh was trying to tell me (I'm not a
programmer) - I need real laymen terms like how you responded.

Ok, here's the Sql. I just realized the field names are a bit different -
DD2 table is FirstName LastName and RD table if NameFirst and NameLast

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] & "
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS [CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
" " & [LastName], RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], [DD2 LIST].[Lead Date], [DD2 LIST].[Active
Recruit]
HAVING ((([DD2 LIST].[Lead Date])>=#1/1/2008# And ([DD2 LIST].[Lead
Date])<=#12/31/2008#) AND (([DD2 LIST].[Active Recruit])=-1))
ORDER BY [DD2 LIST].[Lead Date] DESC;


I still get the error message, "You tried to execute a query that does not
include specified expression 'RD. [RD].[NameFirst] & " "& [RD].[NameLast]' as
part of an aggregate function, which I don't know what the hell that means.

Thanks for your help.

This should work in a query:
RDName: [RD].[FirstName] &" "& [RD].[LastName]
What do you mean by "doesn't work"?

How about providing the complete SQL view of the query that "doesn't work"?

--
Duane Hookom
Microsoft Access MVP

I have 2 tables that have FirstName and LastName in each table. I am making
an expression to combine the First and last name together, but Access doesn't
know which table I'm referring to. I've done this before but can't seem to
get the sytax right.

RDName: [FirstName] &" "& [LastName] is my expression. The table name is
RD. How and where do I insert RD in this expression so the query will run.
I've tried
RDName: [RD].[FirstName] &" "& [RD].[LastName] and it doesn't work. I've
also tried it with [RD]![FirstName] &" "& [RD]![LastName] and that doesn't
work either.

Can somebody get me straight here?

Much appreciated.

 

[Douglas J. Steele]

Whenever you're using aggregate functions such as Count, every field in the
query must either be a call to one of the aggregate functions, or must
appear in the GROUP BY list. You've got RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName in your SELECT list, but it's not in your GROUP BY
list.

Incidentally, your query would be more efficient if your HAVING clause was a
WHERE clause. WHERE clauses get evaluated before the aggregation takes
place, HAVING clauses get evaluated after the aggregation takes place. (Yes,
I realize that the query builder always uses HAVING, but that doesn't make
it correct!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] & "
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
"
" & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Duane:

I couldn't follow what Michel Walsh was trying to tell me (I'm not a
programmer) - I need real laymen terms like how you responded.

Ok, here's the Sql. I just realized the field names are a bit different -
DD2 table is FirstName LastName and RD table if NameFirst and NameLast

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
" " & [LastName], RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], [DD2 LIST].[Lead Date], [DD2 LIST].[Active
Recruit]
HAVING ((([DD2 LIST].[Lead Date])>=#1/1/2008# And ([DD2 LIST].[Lead
Date])<=#12/31/2008#) AND (([DD2 LIST].[Active Recruit])=-1))
ORDER BY [DD2 LIST].[Lead Date] DESC;


I still get the error message, "You tried to execute a query that does not
include specified expression 'RD. [RD].[NameFirst] & " "& [RD].[NameLast]'
as
part of an aggregate function, which I don't know what the hell that
means.

Thanks for your help.

This should work in a query:
RDName: [RD].[FirstName] &" "& [RD].[LastName]
What do you mean by "doesn't work"?

How about providing the complete SQL view of the query that "doesn't
work"?

--
Duane Hookom
Microsoft Access MVP

I have 2 tables that have FirstName and LastName in each table. I am
making
an expression to combine the First and last name together, but Access
doesn't
know which table I'm referring to. I've done this before but can't
seem to
get the sytax right.

RDName: [FirstName] &" "& [LastName] is my expression. The table name
is
RD. How and where do I insert RD in this expression so the query will
run.
I've tried
RDName: [RD].[FirstName] &" "& [RD].[LastName] and it doesn't work.
I've
also tried it with [RD]![FirstName] &" "& [RD]![LastName] and that
doesn't
work either.

Can somebody get me straight here?

Much appreciated.

 

[Susan May]

Hi Douglas:

I'm trying to learn the logic here so that's for your description. I copied
and pasted the Sql code into my query, and I'm getting an error message
"Invalid bracketing of name 'CountOfLeadDate'. I tried to end it with a ")"
and that didn't work. What bracket is missing?

Again, I appreciate your time and suport.

Susan

Whenever you're using aggregate functions such as Count, every field in the
query must either be a call to one of the aggregate functions, or must
appear in the GROUP BY list. You've got RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName in your SELECT list, but it's not in your GROUP BY
list.

Incidentally, your query would be more efficient if your HAVING clause was a
WHERE clause. WHERE clauses get evaluated before the aggregation takes
place, HAVING clauses get evaluated after the aggregation takes place. (Yes,
I realize that the query builder always uses HAVING, but that doesn't make
it correct!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] & "
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
"
" & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Duane:

I couldn't follow what Michel Walsh was trying to tell me (I'm not a
programmer) - I need real laymen terms like how you responded.

Ok, here's the Sql. I just realized the field names are a bit different -
DD2 table is FirstName LastName and RD table if NameFirst and NameLast

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
" " & [LastName], RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], [DD2 LIST].[Lead Date], [DD2 LIST].[Active
Recruit]
HAVING ((([DD2 LIST].[Lead Date])>=#1/1/2008# And ([DD2 LIST].[Lead
Date])<=#12/31/2008#) AND (([DD2 LIST].[Active Recruit])=-1))
ORDER BY [DD2 LIST].[Lead Date] DESC;


I still get the error message, "You tried to execute a query that does not
include specified expression 'RD. [RD].[NameFirst] & " "& [RD].[NameLast]'
as
part of an aggregate function, which I don't know what the hell that
means.

Thanks for your help.

This should work in a query:
RDName: [RD].[FirstName] &" "& [RD].[LastName]
What do you mean by "doesn't work"?

How about providing the complete SQL view of the query that "doesn't
work"?

--
Duane Hookom
Microsoft Access MVP


:

I have 2 tables that have FirstName and LastName in each table. I am
making
an expression to combine the First and last name together, but Access
doesn't
know which table I'm referring to. I've done this before but can't
seem to
get the sytax right.

RDName: [FirstName] &" "& [LastName] is my expression. The table name
is
RD. How and where do I insert RD in this expression so the query will
run.
I've tried
RDName: [RD].[FirstName] &" "& [RD].[LastName] and it doesn't work.
I've
also tried it with [RD]![FirstName] &" "& [RD]![LastName] and that
doesn't
work either.

Can somebody get me straight here?

Much appreciated.

 

[Douglas J. Steele]

Damn word wrap!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] & "
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], Count(*) AS [CountOfLead Date],
[DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
" " & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;

Note that I also changed it from Count([DD2 LIST].[Lead Date]) to Count(*).
The difference is that the first will ignore rows where [DD2 LIST].[Lead
Date] is null when doing the count, while the second will count all rows,
regardless of the value of [DD2 LIST].[Lead Date].

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas:

I'm trying to learn the logic here so that's for your description. I
copied
and pasted the Sql code into my query, and I'm getting an error message
"Invalid bracketing of name 'CountOfLeadDate'. I tried to end it with a
")"
and that didn't work. What bracket is missing?

Again, I appreciate your time and suport.

Susan

Whenever you're using aggregate functions such as Count, every field in
the
query must either be a call to one of the aggregate functions, or must
appear in the GROUP BY list. You've got RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName in your SELECT list, but it's not in your GROUP
BY
list.

Incidentally, your query would be more efficient if your HAVING clause
was a
WHERE clause. WHERE clauses get evaluated before the aggregation takes
place, HAVING clauses get evaluated after the aggregation takes place.
(Yes,
I realize that the query builder always uses HAVING, but that doesn't
make
it correct!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
"
" & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Duane:

I couldn't follow what Michel Walsh was trying to tell me (I'm not a
programmer) - I need real laymen terms like how you responded.

Ok, here's the Sql. I just realized the field names are a bit
different -
DD2 table is FirstName LastName and RD table if NameFirst and NameLast

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid
=
Source.ID) ON RD.ID = [DD2 LIST].RDid
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " &
[FirstName]
&
" " & [LastName], RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], [DD2 LIST].[Lead Date], [DD2
LIST].[Active
Recruit]
HAVING ((([DD2 LIST].[Lead Date])>=#1/1/2008# And ([DD2 LIST].[Lead
Date])<=#12/31/2008#) AND (([DD2 LIST].[Active Recruit])=-1))
ORDER BY [DD2 LIST].[Lead Date] DESC;


I still get the error message, "You tried to execute a query that does
not
include specified expression 'RD. [RD].[NameFirst] & " "&
[RD].[NameLast]'
as
part of an aggregate function, which I don't know what the hell that
means.

Thanks for your help.

:

This should work in a query:
RDName: [RD].[FirstName] &" "& [RD].[LastName]
What do you mean by "doesn't work"?

How about providing the complete SQL view of the query that "doesn't
work"?

--
Duane Hookom
Microsoft Access MVP


:

I have 2 tables that have FirstName and LastName in each table. I
am
making
an expression to combine the First and last name together, but
Access
doesn't
know which table I'm referring to. I've done this before but can't
seem to
get the sytax right.

RDName: [FirstName] &" "& [LastName] is my expression. The table
name
is
RD. How and where do I insert RD in this expression so the query
will
run.
I've tried
RDName: [RD].[FirstName] &" "& [RD].[LastName] and it doesn't work.
I've
also tried it with [RD]![FirstName] &" "& [RD]![LastName] and that
doesn't
work either.

Can somebody get me straight here?

Much appreciated.

 

[Susan May]

Damn - it's still giving me an error, "You tried to execute a query that does
not include the specified expression ''[DD 2 LIST].[DD2 LIST].[Title] & " "&
[FirstName] &" " &[LastName] as part of an aggregrate function."

Damn word wrap!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] & "
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], Count(*) AS [CountOfLead Date],
[DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
" " & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;

Note that I also changed it from Count([DD2 LIST].[Lead Date]) to Count(*).
The difference is that the first will ignore rows where [DD2 LIST].[Lead
Date] is null when doing the count, while the second will count all rows,
regardless of the value of [DD2 LIST].[Lead Date].

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas:

I'm trying to learn the logic here so that's for your description. I
copied
and pasted the Sql code into my query, and I'm getting an error message
"Invalid bracketing of name 'CountOfLeadDate'. I tried to end it with a
")"
and that didn't work. What bracket is missing?

Again, I appreciate your time and suport.

Susan

Whenever you're using aggregate functions such as Count, every field in
the
query must either be a call to one of the aggregate functions, or must
appear in the GROUP BY list. You've got RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName in your SELECT list, but it's not in your GROUP
BY
list.

Incidentally, your query would be more efficient if your HAVING clause
was a
WHERE clause. WHERE clauses get evaluated before the aggregation takes
place, HAVING clauses get evaluated after the aggregation takes place.
(Yes,
I realize that the query builder always uses HAVING, but that doesn't
make
it correct!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
"
" & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Duane:

I couldn't follow what Michel Walsh was trying to tell me (I'm not a
programmer) - I need real laymen terms like how you responded.

Ok, here's the Sql. I just realized the field names are a bit
different -
DD2 table is FirstName LastName and RD table if NameFirst and NameLast

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid
=
Source.ID) ON RD.ID = [DD2 LIST].RDid
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " &
[FirstName]
&
" " & [LastName], RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], [DD2 LIST].[Lead Date], [DD2
LIST].[Active
Recruit]
HAVING ((([DD2 LIST].[Lead Date])>=#1/1/2008# And ([DD2 LIST].[Lead
Date])<=#12/31/2008#) AND (([DD2 LIST].[Active Recruit])=-1))
ORDER BY [DD2 LIST].[Lead Date] DESC;


I still get the error message, "You tried to execute a query that does
not
include specified expression 'RD. [RD].[NameFirst] & " "&
[RD].[NameLast]'
as
part of an aggregate function, which I don't know what the hell that
means.

Thanks for your help.

:

This should work in a query:
RDName: [RD].[FirstName] &" "& [RD].[LastName]
What do you mean by "doesn't work"?

How about providing the complete SQL view of the query that "doesn't
work"?

--
Duane Hookom
Microsoft Access MVP


:

I have 2 tables that have FirstName and LastName in each table. I
am
making
an expression to combine the First and last name together, but
Access
doesn't
know which table I'm referring to. I've done this before but can't
seem to
get the sytax right.

RDName: [FirstName] &" "& [LastName] is my expression. The table
name
is
RD. How and where do I insert RD in this expression so the query
will
run.
I've tried
RDName: [RD].[FirstName] &" "& [RD].[LastName] and it doesn't work.
I've
also tried it with [RD]![FirstName] &" "& [RD]![LastName] and that
doesn't
work either.

Can somebody get me straight here?

Much appreciated.

 

[Duane Hookom]

Try:
SELECT Source.Source,
[DD2 LIST].[Title] & " " & [FirstName] & " " & [LastName] AS Full_First_Last,
[RD].[NameFirst] & " " & [RD].[NameLast] AS RDName,
RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2 LIST].[Lead_Sheet_Return Date],
Count(*) AS [CountOfLead Date],
[DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source,
[DD2 LIST].[Title] & " " & [FirstName] & " " & [LastName],
[RD].[NameFirst] & " " & [RD].[NameLast],
RD.RDTitle,[DD2 LIST].[Lead_Date_To RD],
[DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;

--
Duane Hookom
Microsoft Access MVP

Damn - it's still giving me an error, "You tried to execute a query that does
not include the specified expression ''[DD 2 LIST].[DD2 LIST].[Title] & " "&
[FirstName] &" " &[LastName] as part of an aggregrate function."

Damn word wrap!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] & "
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], Count(*) AS [CountOfLead Date],
[DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
" " & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;

Note that I also changed it from Count([DD2 LIST].[Lead Date]) to Count(*).
The difference is that the first will ignore rows where [DD2 LIST].[Lead
Date] is null when doing the count, while the second will count all rows,
regardless of the value of [DD2 LIST].[Lead Date].

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas:

I'm trying to learn the logic here so that's for your description. I
copied
and pasted the Sql code into my query, and I'm getting an error message
"Invalid bracketing of name 'CountOfLeadDate'. I tried to end it with a
")"
and that didn't work. What bracket is missing?

Again, I appreciate your time and suport.

Susan

:

Whenever you're using aggregate functions such as Count, every field in
the
query must either be a call to one of the aggregate functions, or must
appear in the GROUP BY list. You've got RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName in your SELECT list, but it's not in your GROUP
BY
list.

Incidentally, your query would be more efficient if your HAVING clause
was a
WHERE clause. WHERE clauses get evaluated before the aggregation takes
place, HAVING clauses get evaluated after the aggregation takes place.
(Yes,
I realize that the query builder always uses HAVING, but that doesn't
make
it correct!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
"
" & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Duane:

I couldn't follow what Michel Walsh was trying to tell me (I'm not a
programmer) - I need real laymen terms like how you responded.

Ok, here's the Sql. I just realized the field names are a bit
different -
DD2 table is FirstName LastName and RD table if NameFirst and NameLast

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid
=
Source.ID) ON RD.ID = [DD2 LIST].RDid
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " &
[FirstName]
&
" " & [LastName], RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], [DD2 LIST].[Lead Date], [DD2
LIST].[Active
Recruit]
HAVING ((([DD2 LIST].[Lead Date])>=#1/1/2008# And ([DD2 LIST].[Lead
Date])<=#12/31/2008#) AND (([DD2 LIST].[Active Recruit])=-1))
ORDER BY [DD2 LIST].[Lead Date] DESC;


I still get the error message, "You tried to execute a query that does
not
include specified expression 'RD. [RD].[NameFirst] & " "&
[RD].[NameLast]'
as
part of an aggregate function, which I don't know what the hell that
means.

Thanks for your help.

:

This should work in a query:
RDName: [RD].[FirstName] &" "& [RD].[LastName]
What do you mean by "doesn't work"?

How about providing the complete SQL view of the query that "doesn't
work"?

--
Duane Hookom
Microsoft Access MVP


:

I have 2 tables that have FirstName and LastName in each table. I
am
making
an expression to combine the First and last name together, but
Access
doesn't
know which table I'm referring to. I've done this before but can't
seem to
get the sytax right.

RDName: [FirstName] &" "& [LastName] is my expression. The table
name
is
RD. How and where do I insert RD in this expression so the query
will
run.
I've tried
RDName: [RD].[FirstName] &" "& [RD].[LastName] and it doesn't work.
I've
also tried it with [RD]![FirstName] &" "& [RD]![LastName] and that
doesn't
work either.

Can somebody get me straight here?

Much appreciated.

 

[Susan May]

That worked!

Thank you so much Duane!

Susan

Try:
SELECT Source.Source,
[DD2 LIST].[Title] & " " & [FirstName] & " " & [LastName] AS Full_First_Last,
[RD].[NameFirst] & " " & [RD].[NameLast] AS RDName,
RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2 LIST].[Lead_Sheet_Return Date],
Count(*) AS [CountOfLead Date],
[DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source,
[DD2 LIST].[Title] & " " & [FirstName] & " " & [LastName],
[RD].[NameFirst] & " " & [RD].[NameLast],
RD.RDTitle,[DD2 LIST].[Lead_Date_To RD],
[DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;

--
Duane Hookom
Microsoft Access MVP

Damn - it's still giving me an error, "You tried to execute a query that does
not include the specified expression ''[DD 2 LIST].[DD2 LIST].[Title] & " "&
[FirstName] &" " &[LastName] as part of an aggregrate function."

Damn word wrap!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] & "
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], Count(*) AS [CountOfLead Date],
[DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
" " & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;

Note that I also changed it from Count([DD2 LIST].[Lead Date]) to Count(*).
The difference is that the first will ignore rows where [DD2 LIST].[Lead
Date] is null when doing the count, while the second will count all rows,
regardless of the value of [DD2 LIST].[Lead Date].

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Douglas:

I'm trying to learn the logic here so that's for your description. I
copied
and pasted the Sql code into my query, and I'm getting an error message
"Invalid bracketing of name 'CountOfLeadDate'. I tried to end it with a
")"
and that didn't work. What bracket is missing?

Again, I appreciate your time and suport.

Susan

:

Whenever you're using aggregate functions such as Count, every field in
the
query must either be a call to one of the aggregate functions, or must
appear in the GROUP BY list. You've got RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName in your SELECT list, but it's not in your GROUP
BY
list.

Incidentally, your query would be more efficient if your HAVING clause
was a
WHERE clause. WHERE clauses get evaluated before the aggregation takes
place, HAVING clauses get evaluated after the aggregation takes place.
(Yes,
I realize that the query builder always uses HAVING, but that doesn't
make
it correct!

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName] &
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid =
Source.ID) ON RD.ID = [DD2 LIST].RDid
WHERE [DD2 LIST].[Lead Date]>=#1/1/2008#
AND [DD2 LIST].[Lead Date]<=#12/31/2008#
AND [DD2 LIST].[Active Recruit]=-1
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
"
" & [LastName], RD.[RD].[NameFirst] & " " & [RD].[NameLast], RD.RDTitle,
[DD2 LIST].[Lead_Date_To RD], [DD2 LIST].[Lead_Sheet_Return Date],
[DD2 LIST].[Lead Date]
ORDER BY [DD2 LIST].[Lead Date] DESC;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Duane:

I couldn't follow what Michel Walsh was trying to tell me (I'm not a
programmer) - I need real laymen terms like how you responded.

Ok, here's the Sql. I just realized the field names are a bit
different -
DD2 table is FirstName LastName and RD table if NameFirst and NameLast

SELECT Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " & [FirstName]
&
"
" & [LastName] AS Full_First_Last, RD.[RD].[NameFirst] & " " &
[RD].[NameLast] AS RDName, RD.RDTitle, [DD2 LIST].[Lead_Date_To RD],
[DD2
LIST].[Lead_Sheet_Return Date], Count([DD2 LIST].[Lead Date]) AS
[CountOfLead
Date], [DD2 LIST].[Lead Date]
FROM RD INNER JOIN ([DD2 LIST] LEFT JOIN Source ON [DD2 LIST].Sourceid
=
Source.ID) ON RD.ID = [DD2 LIST].RDid
GROUP BY Source.Source, [DD2 LIST].[DD2 LIST].[Title] & " " &
[FirstName]
&
" " & [LastName], RD.RDTitle, [DD2 LIST].[Lead_Date_To RD], [DD2
LIST].[Lead_Sheet_Return Date], [DD2 LIST].[Lead Date], [DD2
LIST].[Active
Recruit]
HAVING ((([DD2 LIST].[Lead Date])>=#1/1/2008# And ([DD2 LIST].[Lead
Date])<=#12/31/2008#) AND (([DD2 LIST].[Active Recruit])=-1))
ORDER BY [DD2 LIST].[Lead Date] DESC;


I still get the error message, "You tried to execute a query that does
not
include specified expression 'RD. [RD].[NameFirst] & " "&
[RD].[NameLast]'
as
part of an aggregate function, which I don't know what the hell that
means.

Thanks for your help.

:

This should work in a query:
RDName: [RD].[FirstName] &" "& [RD].[LastName]
What do you mean by "doesn't work"?

How about providing the complete SQL view of the query that "doesn't
work"?

--
Duane Hookom
Microsoft Access MVP


:

I have 2 tables that have FirstName and LastName in each table. I
am
making
an expression to combine the First and last name together, but
Access
doesn't
know which table I'm referring to. I've done this before but can't
seem to
get the sytax right.

RDName: [FirstName] &" "& [LastName] is my expression. The table
name
is
RD. How and where do I insert RD in this expression so the query
will
run.
I've tried
RDName: [RD].[FirstName] &" "& [RD].[LastName] and it doesn't work.
I've
also tried it with [RD]![FirstName] &" "& [RD]![LastName] and that
doesn't
work either.

Can somebody get me straight here?

Much appreciated.