SQL Error

L

LMB

Hi Guys,

I have a database a programmer created years ago in Access 2.0. It is now
converted to Access 2000 and I want to join a few tables together to get a
report. When I try to concantenate the physician name, I get the following
error when trying to run the query. I know it's because LastName and
FirstName are also fields in the client table but I don't know how to get
the my Name field to use the physician table to pull the info from. I use
the design grid when creating my queries. I did select the Physician Table
to get the info from but I must need to fix the SQL, I went to the website
below but don't really understand the language enough to know just how to
fix it. I was thinking on the way home from work...Do I need to make a
query with just the physicians and physician link in it and concantenate the
name in there and then join that to this query?.....Thanks, Linda!

Error: The specified field 'LastName' could refer to more than one table
listed in the From clause of your SQL statement.

http://www.sjsoft.com/docs/jworkbook/sqlreference.htm#from


SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName,
Physicians.FirstName, Physicians.[FirstName] & [LastName] AS Expr1
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON
Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule ON
Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID =
[Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName,
Physicians.FirstName, Physicians.[PhysiciFirstName] & [LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between
DateValue([Beginning Date]) And DateValue([Ending Date])) AND
((Schedule.Description)="needs final visit scheduled" Or
(Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs
scheduled" Or (Schedule.Description)="next appt." Or
(Schedule.Description)="Make inactive if no phone call" Or
(Schedule.Description)="needs 1st Phone Follow Up" Or
(Schedule.Description)="Needs Final Phone Follow Up" Or
(Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName,
Clients.FirstName;
 
D

Duane Hookom

This
Physicians.[FirstName] & [LastName] AS Expr1
Should be replaced by
Physicians.[FirstName] & Physicians.[LastName] AS PhysFullName
 
L

LMB

I think I did what you said but I get the same error. Error: The specified field 'LastName' could refer to more than one table listed in the From clause of your SQL statement.
Here is my new sql...

SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date, Schedule.Description, Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact, Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName, Physicians.FirstName, Physicians.[FirstName] & Physicians.[LastName] AS PhysFullName
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule ON Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID = [Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description, Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact, Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName, Physicians.FirstName, [Physicians].[FirstName] & [LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between DateValue([Beginning Date]) And DateValue([Ending Date])) AND ((Schedule.Description)="needs final visit scheduled" Or (Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs scheduled" Or (Schedule.Description)="next appt." Or (Schedule.Description)="Make inactive if no phone call" Or (Schedule.Description)="needs 1st Phone Follow Up" Or (Schedule.Description)="Needs Final Phone Follow Up" Or (Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName, Clients.FirstName;

This
Physicians.[FirstName] & [LastName] AS Expr1
Should be replaced by
Physicians.[FirstName] & Physicians.[LastName] AS PhysFullName

--
Duane Hookom
MS Access MVP
--

LMB said:
Hi Guys,

I have a database a programmer created years ago in Access 2.0. It is now
converted to Access 2000 and I want to join a few tables together to get a
report. When I try to concantenate the physician name, I get the
following error when trying to run the query. I know it's because
LastName and FirstName are also fields in the client table but I don't
know how to get the my Name field to use the physician table to pull the
info from. I use the design grid when creating my queries. I did select
the Physician Table to get the info from but I must need to fix the SQL, I
went to the website below but don't really understand the language enough
to know just how to fix it. I was thinking on the way home from work...Do
I need to make a query with just the physicians and physician link in it
and concantenate the name in there and then join that to this
query?.....Thanks, Linda!

Error: The specified field 'LastName' could refer to more than one table
listed in the From clause of your SQL statement.

http://www.sjsoft.com/docs/jworkbook/sqlreference.htm#from


SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date,
Schedule.Description, Schedule.Letter, Clients.LastName,
Clients.FirstName, Clients.Contact, Clients.HomePhone, [Client-Physician
Link].PhysicianID, Physicians.LastName, Physicians.FirstName,
Physicians.[FirstName] & [LastName] AS Expr1
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON
Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule
ON Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID =
[Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID,
Physicians.LastName, Physicians.FirstName, Physicians.[PhysiciFirstName] &
[LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between
DateValue([Beginning Date]) And DateValue([Ending Date])) AND
((Schedule.Description)="needs final visit scheduled" Or
(Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs
scheduled" Or (Schedule.Description)="next appt." Or
(Schedule.Description)="Make inactive if no phone call" Or
(Schedule.Description)="needs 1st Phone Follow Up" Or
(Schedule.Description)="Needs Final Phone Follow Up" Or
(Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName,
Clients.FirstName;
Click to expand...
 
D

Duane Hookom

Your group by has:
[Physicians].[FirstName] & [LastName]
which needs to be change to
[Physicians].[FirstName] & [Physicians].[LastName]

This is one of the reasons why I don't have the same field name in two
different tables in an MDB.


--
Duane Hookom
MS Access MVP
--

I think I did what you said but I get the same error. Error: The specified
field 'LastName' could refer to more than one table listed in the From
clause of your SQL statement.
Here is my new sql...

SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName,
Physicians.FirstName, Physicians.[FirstName] & Physicians.[LastName] AS
PhysFullName
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON
Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule ON
Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID =
[Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName,
Physicians.FirstName, [Physicians].[FirstName] & [LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between
DateValue([Beginning Date]) And DateValue([Ending Date])) AND
((Schedule.Description)="needs final visit scheduled" Or
(Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs
scheduled" Or (Schedule.Description)="next appt." Or
(Schedule.Description)="Make inactive if no phone call" Or
(Schedule.Description)="needs 1st Phone Follow Up" Or
(Schedule.Description)="Needs Final Phone Follow Up" Or
(Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName,
Clients.FirstName;

This
Physicians.[FirstName] & [LastName] AS Expr1
Should be replaced by
Physicians.[FirstName] & Physicians.[LastName] AS PhysFullName

--
Duane Hookom
MS Access MVP
--

LMB said:
Hi Guys,

I have a database a programmer created years ago in Access 2.0. It is now
converted to Access 2000 and I want to join a few tables together to get a
report. When I try to concantenate the physician name, I get the
following error when trying to run the query. I know it's because
LastName and FirstName are also fields in the client table but I don't
know how to get the my Name field to use the physician table to pull the
info from. I use the design grid when creating my queries. I did select
the Physician Table to get the info from but I must need to fix the SQL, I
went to the website below but don't really understand the language enough
to know just how to fix it. I was thinking on the way home from work...Do
I need to make a query with just the physicians and physician link in it
and concantenate the name in there and then join that to this
query?.....Thanks, Linda!

Error: The specified field 'LastName' could refer to more than one table
listed in the From clause of your SQL statement.

http://www.sjsoft.com/docs/jworkbook/sqlreference.htm#from


SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date,
Schedule.Description, Schedule.Letter, Clients.LastName,
Clients.FirstName, Clients.Contact, Clients.HomePhone, [Client-Physician
Link].PhysicianID, Physicians.LastName, Physicians.FirstName,
Physicians.[FirstName] & [LastName] AS Expr1
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON
Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule
ON Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID =
[Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID,
Physicians.LastName, Physicians.FirstName, Physicians.[PhysiciFirstName] &
[LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between
DateValue([Beginning Date]) And DateValue([Ending Date])) AND
((Schedule.Description)="needs final visit scheduled" Or
(Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs
scheduled" Or (Schedule.Description)="next appt." Or
(Schedule.Description)="Make inactive if no phone call" Or
(Schedule.Description)="needs 1st Phone Follow Up" Or
(Schedule.Description)="Needs Final Phone Follow Up" Or
(Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName,
Clients.FirstName;
Click to expand...
 
L

LMB

I think I give up for tonight. I tried it with brackets around both physician and Name fields, without brackets around physician fields, I tried using the builder thingy and it added an exclamation mark between the [Physicians]![FirstName], and that didn't work, then I tried to insert the expression into a control on a report and it didn't work either. I remember reading in these groups or maybe on the Access Bible website about not naming any fields the same in 2 different tables and I have remembered that but now I know I'll never forget it. This database was made by a "real" access programmer who charged a lot of money to make this for us about 10 years ago.

Thanks, I'll keep trying until I get it by golly.

Linda
Your group by has:
[Physicians].[FirstName] & [LastName]
which needs to be change to
[Physicians].[FirstName] & [Physicians].[LastName]

This is one of the reasons why I don't have the same field name in two
different tables in an MDB.


--
Duane Hookom
MS Access MVP
--

I think I did what you said but I get the same error. Error: The specified
field 'LastName' could refer to more than one table listed in the From
clause of your SQL statement.
Here is my new sql...

SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName,
Physicians.FirstName, Physicians.[FirstName] & Physicians.[LastName] AS
PhysFullName
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON
Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule ON
Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID =
[Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName,
Physicians.FirstName, [Physicians].[FirstName] & [LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between
DateValue([Beginning Date]) And DateValue([Ending Date])) AND
((Schedule.Description)="needs final visit scheduled" Or
(Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs
scheduled" Or (Schedule.Description)="next appt." Or
(Schedule.Description)="Make inactive if no phone call" Or
(Schedule.Description)="needs 1st Phone Follow Up" Or
(Schedule.Description)="Needs Final Phone Follow Up" Or
(Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName,
Clients.FirstName;

This
Physicians.[FirstName] & [LastName] AS Expr1
Should be replaced by
Physicians.[FirstName] & Physicians.[LastName] AS PhysFullName

--
Duane Hookom
MS Access MVP
--

LMB said:
Hi Guys,

I have a database a programmer created years ago in Access 2.0. It is now
converted to Access 2000 and I want to join a few tables together to get a
report. When I try to concantenate the physician name, I get the
following error when trying to run the query. I know it's because
LastName and FirstName are also fields in the client table but I don't
know how to get the my Name field to use the physician table to pull the
info from. I use the design grid when creating my queries. I did select
the Physician Table to get the info from but I must need to fix the SQL, I
went to the website below but don't really understand the language enough
to know just how to fix it. I was thinking on the way home from work...Do
I need to make a query with just the physicians and physician link in it
and concantenate the name in there and then join that to this
query?.....Thanks, Linda!

Error: The specified field 'LastName' could refer to more than one table
listed in the From clause of your SQL statement.

http://www.sjsoft.com/docs/jworkbook/sqlreference.htm#from


SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date,
Schedule.Description, Schedule.Letter, Clients.LastName,
Clients.FirstName, Clients.Contact, Clients.HomePhone, [Client-Physician
Link].PhysicianID, Physicians.LastName, Physicians.FirstName,
Physicians.[FirstName] & [LastName] AS Expr1
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON
Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule
ON Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID =
[Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID,
Physicians.LastName, Physicians.FirstName, Physicians.[PhysiciFirstName] &
[LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between
DateValue([Beginning Date]) And DateValue([Ending Date])) AND
((Schedule.Description)="needs final visit scheduled" Or
(Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs
scheduled" Or (Schedule.Description)="next appt." Or
(Schedule.Description)="Make inactive if no phone call" Or
(Schedule.Description)="needs 1st Phone Follow Up" Or
(Schedule.Description)="Needs Final Phone Follow Up" Or
(Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName,
Clients.FirstName;
Click to expand...
 
D

Duane Hookom

Try this SQL where I have aliased the client and physician first and last
names. You may need to update controls in your report or form to match the
new field names.
If your Schedule.Description comes from a lookup table, I would add a field
to the table that determines whether it should be included in the query.
This would get rid of all the "Description ="..." or Description = "..." "
which is horrible to maintain.

SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName as CliLastName,
Clients.FirstName as CliFirstName, Clients.Contact, Clients.HomePhone,
[Client-Physician Link].PhysicianID, Physicians.LastName as PhyLastName,
Physicians.FirstName as PhyFirstName, Physicians.[FirstName] &
Physicians.[LastName] AS PhysFullName
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON
Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule ON
Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID =
[Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName,
Physicians.FirstName, [Physicians].[FirstName] & [Physicians].[LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between
DateValue([Beginning Date]) And DateValue([Ending Date]))
AND ((Schedule.Description)="needs final visit scheduled" Or
(Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs
scheduled" Or (Schedule.Description)="next appt." Or
(Schedule.Description)="Make inactive if no phone call" Or
(Schedule.Description)="needs 1st Phone Follow Up" Or
(Schedule.Description)="Needs Final Phone Follow Up" Or
(Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName,
Clients.FirstName;


--
Duane Hookom
MS Access MVP
--

I think I give up for tonight. I tried it with brackets around both
physician and Name fields, without brackets around physician fields, I tried
using the builder thingy and it added an exclamation mark between the
[Physicians]![FirstName], and that didn't work, then I tried to insert the
expression into a control on a report and it didn't work either. I remember
reading in these groups or maybe on the Access Bible website about not
naming any fields the same in 2 different tables and I have remembered that
but now I know I'll never forget it. This database was made by a "real"
access programmer who charged a lot of money to make this for us about 10
years ago.

Thanks, I'll keep trying until I get it by golly.

Linda
Your group by has:
[Physicians].[FirstName] & [LastName]
which needs to be change to
[Physicians].[FirstName] & [Physicians].[LastName]

This is one of the reasons why I don't have the same field name in two
different tables in an MDB.


--
Duane Hookom
MS Access MVP
--

I think I did what you said but I get the same error. Error: The specified
field 'LastName' could refer to more than one table listed in the From
clause of your SQL statement.
Here is my new sql...

SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName,
Physicians.FirstName, Physicians.[FirstName] & Physicians.[LastName] AS
PhysFullName
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON
Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule ON
Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID =
[Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID, Physicians.LastName,
Physicians.FirstName, [Physicians].[FirstName] & [LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between
DateValue([Beginning Date]) And DateValue([Ending Date])) AND
((Schedule.Description)="needs final visit scheduled" Or
(Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs
scheduled" Or (Schedule.Description)="next appt." Or
(Schedule.Description)="Make inactive if no phone call" Or
(Schedule.Description)="needs 1st Phone Follow Up" Or
(Schedule.Description)="Needs Final Phone Follow Up" Or
(Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName,
Clients.FirstName;

This
Physicians.[FirstName] & [LastName] AS Expr1
Should be replaced by
Physicians.[FirstName] & Physicians.[LastName] AS PhysFullName

--
Duane Hookom
MS Access MVP
--

LMB said:
Hi Guys,

I have a database a programmer created years ago in Access 2.0. It is now
converted to Access 2000 and I want to join a few tables together to get a
report. When I try to concantenate the physician name, I get the
following error when trying to run the query. I know it's because
LastName and FirstName are also fields in the client table but I don't
know how to get the my Name field to use the physician table to pull the
info from. I use the design grid when creating my queries. I did select
the Physician Table to get the info from but I must need to fix the SQL, I
went to the website below but don't really understand the language enough
to know just how to fix it. I was thinking on the way home from work...Do
I need to make a query with just the physicians and physician link in it
and concantenate the name in there and then join that to this
query?.....Thanks, Linda!

Error: The specified field 'LastName' could refer to more than one table
listed in the From clause of your SQL statement.

http://www.sjsoft.com/docs/jworkbook/sqlreference.htm#from


SELECT DISTINCTROW Clients.[Inactive?], Schedule.Date,
Schedule.Description, Schedule.Letter, Clients.LastName,
Clients.FirstName, Clients.Contact, Clients.HomePhone, [Client-Physician
Link].PhysicianID, Physicians.LastName, Physicians.FirstName,
Physicians.[FirstName] & [LastName] AS Expr1
FROM Physicians INNER JOIN ((Clients INNER JOIN [Client-Physician Link] ON
Clients.ClientID = [Client-Physician Link].ClientID) INNER JOIN Schedule
ON Clients.ClientID = Schedule.ClientID) ON Physicians.PhysicianID =
[Client-Physician Link].PhysicianID
GROUP BY Clients.[Inactive?], Schedule.Date, Schedule.Description,
Schedule.Letter, Clients.LastName, Clients.FirstName, Clients.Contact,
Clients.HomePhone, [Client-Physician Link].PhysicianID,
Physicians.LastName, Physicians.FirstName, Physicians.[PhysiciFirstName] &
[LastName]
HAVING (((Clients.[Inactive?])=No) AND ((Schedule.Date) Between
DateValue([Beginning Date]) And DateValue([Ending Date])) AND
((Schedule.Description)="needs final visit scheduled" Or
(Schedule.Description)="needs phone f/u" Or (Schedule.Description)="needs
scheduled" Or (Schedule.Description)="next appt." Or
(Schedule.Description)="Make inactive if no phone call" Or
(Schedule.Description)="needs 1st Phone Follow Up" Or
(Schedule.Description)="Needs Final Phone Follow Up" Or
(Schedule.Description)="Need Plan Back"))
ORDER BY Clients.[Inactive?] DESC , Schedule.Date, Clients.LastName,
Clients.FirstName;
Click to expand...
 
L

LMB

That worked! I don't totally understand what you mean by description etc...but I'll keep this and refer to it as I move along in my understanding.

I figured out how to change my report and I also figured out how to make a space between the physicians first and last name. I had to do this [name]& " "&[name]. What type of programming class would help me to understand this? I initially used [name]& " "[name] This didn't work and I had to find the answer from a google search instead of knowing that I needed another &.

Thanks so much!
Linda
Try this SQL where I have aliased the client and physician first and last
names. You may need to update controls in your report or form to match the
new field names.
If your Schedule.Description comes from a lookup table, I would add a field
to the table that determines whether it should be included in the query.
This would get rid of all the "Description ="..." or Description = "..." "
which is horrible to maintain.
 
D

Duane Hookom

I would only take a programming class from an instructor who works at least
part-time as an Access programmer or contractor.

--
Duane Hookom
MS Access MVP
--

That worked! I don't totally understand what you mean by description
etc...but I'll keep this and refer to it as I move along in my
understanding.

I figured out how to change my report and I also figured out how to make a
space between the physicians first and last name. I had to do this [name]&
" "&[name]. What type of programming class would help me to understand
this? I initially used [name]& " "[name] This didn't work and I had to
find the answer from a google search instead of knowing that I needed
another &.

Thanks so much!
Linda
Try this SQL where I have aliased the client and physician first and last
names. You may need to update controls in your report or form to match the
new field names.
If your Schedule.Description comes from a lookup table, I would add a field
to the table that determines whether it should be included in the query.
This would get rid of all the "Description ="..." or Description = "..." "
which is horrible to maintain.
 
L

LMB

Ok..I am working with someone who does Access but I thought if I had a basic understanding of programming, I would understand a little better how to get what I want. Concatenating a field seems like one of the simplest of things to do but it doesn't make sense to me that you need to use 2 &s. I guess I can think of the & as elmers glue and the first and last name as 2 boxes I want to glue together. If I want to keep the boxes a little bit apart (adding a space or comma), I need to put in a connecter and there and then the glue needs to be on both boxes.

Is there any other time you use a & to connect anything?

Thanks,
Linda
I would only take a programming class from an instructor who works at least
part-time as an Access programmer or contractor.

--
Duane Hookom
MS Access MVP
--

That worked! I don't totally understand what you mean by description
etc...but I'll keep this and refer to it as I move along in my
understanding.

I figured out how to change my report and I also figured out how to make a
space between the physicians first and last name. I had to do this [name]&
" "&[name]. What type of programming class would help me to understand
this? I initially used [name]& " "[name] This didn't work and I had to
find the answer from a google search instead of knowing that I needed
another &.

Thanks so much!
Linda
Try this SQL where I have aliased the client and physician first and last
names. You may need to update controls in your report or form to match the
new field names.
If your Schedule.Description comes from a lookup table, I would add a field
to the table that determines whether it should be included in the query.
This would get rid of all the "Description ="..." or Description = "..." "
which is horrible to maintain.
 
L

LMB

So does the & sign create the blank space or is the space created with the " " ? Why wouldn't [field]& &[Field] create the space? Romulans are supposed to be very intelligent, perhaps I need to change my name to Pakled Queen <g>

Thanks,
Linda
Using the "&" is very basic. I couldn't begin to list all the ways we use
it.

If you had 7 blocks with the letters that spelled out "Romulan" and 5 that
spelled "Queen" and placed them in order right next to each other, they will
display:
RomulanQueen. You need to add another block that is a blank so that you get
Romulan Queen.

--
Duane Hookom
MS Access MVP


Ok..I am working with someone who does Access but I thought if I had a basic
understanding of programming, I would understand a little better how to get
what I want. Concatenating a field seems like one of the simplest of things
to do but it doesn't make sense to me that you need to use 2 &s. I guess I
can think of the & as elmers glue and the first and last name as 2 boxes I
want to glue together. If I want to keep the boxes a little bit apart
(adding a space or comma), I need to put in a connecter and there and then
the glue needs to be on both boxes.

Is there any other time you use a & to connect anything?

Thanks,
Linda
I would only take a programming class from an instructor who works at least
part-time as an Access programmer or contractor.

--
Duane Hookom
MS Access MVP
--

That worked! I don't totally understand what you mean by description
etc...but I'll keep this and refer to it as I move along in my
understanding.

I figured out how to change my report and I also figured out how to make a
space between the physicians first and last name. I had to do this [name]&
" "&[name]. What type of programming class would help me to understand
this? I initially used [name]& " "[name] This didn't work and I had to
find the answer from a google search instead of knowing that I needed
another &.

Thanks so much!
Linda
Try this SQL where I have aliased the client and physician first and last
names. You may need to update controls in your report or form to match the
new field names.
If your Schedule.Description comes from a lookup table, I would add a field
to the table that determines whether it should be included in the query.
This would get rid of all the "Description ="..." or Description = "..." "
which is horrible to maintain.
 
D

Duane Hookom

Just play with the expressions and see what works and what doesn't.

--
Duane Hookom
MS Access MVP


So does the & sign create the blank space or is the space created with the "
" ? Why wouldn't [field]& &[Field] create the space? Romulans are
supposed to be very intelligent, perhaps I need to change my name to Pakled
Queen <g>

Thanks,
Linda
Using the "&" is very basic. I couldn't begin to list all the ways we use
it.

If you had 7 blocks with the letters that spelled out "Romulan" and 5 that
spelled "Queen" and placed them in order right next to each other, they will
display:
RomulanQueen. You need to add another block that is a blank so that you get
Romulan Queen.

--
Duane Hookom
MS Access MVP


Ok..I am working with someone who does Access but I thought if I had a basic
understanding of programming, I would understand a little better how to get
what I want. Concatenating a field seems like one of the simplest of things
to do but it doesn't make sense to me that you need to use 2 &s. I guess I
can think of the & as elmers glue and the first and last name as 2 boxes I
want to glue together. If I want to keep the boxes a little bit apart
(adding a space or comma), I need to put in a connecter and there and then
the glue needs to be on both boxes.

Is there any other time you use a & to connect anything?

Thanks,
Linda
I would only take a programming class from an instructor who works at least
part-time as an Access programmer or contractor.

--
Duane Hookom
MS Access MVP
--

That worked! I don't totally understand what you mean by description
etc...but I'll keep this and refer to it as I move along in my
understanding.

I figured out how to change my report and I also figured out how to make a
space between the physicians first and last name. I had to do this [name]&
" "&[name]. What type of programming class would help me to understand
this? I initially used [name]& " "[name] This didn't work and I had to
find the answer from a google search instead of knowing that I needed
another &.

Thanks so much!
Linda
Try this SQL where I have aliased the client and physician first and last
names. You may need to update controls in your report or form to match the
new field names.
If your Schedule.Description comes from a lookup table, I would add a field
to the table that determines whether it should be included in the query.
This would get rid of all the "Description ="..." or Description = "..." "
which is horrible to maintain.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Avoid Duplicates with an Append Query 1
Help with Duplicate Output Alias Error 'Exp1' 0
DateDiff returning incorrect years 11
Customer Query 1
SQL 0
Problems with dates in query 2
Formula will not work 14
syntax error with multiple inner join query - Access 2003 8

Top