SQL - WHERE Help

[Schwimms]

HI,

I have created a Unioin query but I need some help to specify "Where". I
want it to join all of PICSSHIP GSP if the dates are greater then the max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP HISTORY];

That where line doesn't work.

 

[Douglas J. Steele]

You sure you need a UNION query? Union queries don't know anything about the
various tables within the subselects, which means that you cannot compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP Shipped
Date has embedded spaces in it, it must be enclosed in square brackets (like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM [GSP
HISTORY])

 

[akphidelt]

It has to look something like

((([GSP Shipped Date].[PICSSHIP GSP]>[GSP Shipped Date].[GSP HISTORY]))

This is untested but i think it should look something similiar to this.

 

[Schwimms]

I do believe I need a Union Query because What I am doing is making a report
for a user that is not familiar with access. It takes the historical and adds
the updated data, without going into access. I can import the Union query
from excel.

I have also double checked that my orginal data total rows is the same as
what is output.

I have also double checked both of your guys WHERE statements, and I was
unable to get them to work.

You sure you need a UNION query? Union queries don't know anything about the
various tables within the subselects, which means that you cannot compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP Shipped
Date has embedded spaces in it, it must be enclosed in square brackets (like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM [GSP
HISTORY])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI,

I have created a Unioin query but I need some help to specify "Where". I
want it to join all of PICSSHIP GSP if the dates are greater then the max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP HISTORY];

That where line doesn't work.

 

[Schwimms]

Woops I jumped the gun, Doug your WHERE statement worked.

You sure you need a UNION query? Union queries don't know anything about the
various tables within the subselects, which means that you cannot compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP Shipped
Date has embedded spaces in it, it must be enclosed in square brackets (like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM [GSP
HISTORY])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI,

I have created a Unioin query but I need some help to specify "Where". I
want it to join all of PICSSHIP GSP if the dates are greater then the max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP HISTORY];

That where line doesn't work.

 

[Schwimms]

Doh! Union queries cannot be IMPORTED in when I am in excel. Is there a query
that will combine these 2 data sources and allow me to IMPORT them in from
excel.

You sure you need a UNION query? Union queries don't know anything about the
various tables within the subselects, which means that you cannot compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP Shipped
Date has embedded spaces in it, it must be enclosed in square brackets (like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM [GSP
HISTORY])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI,

I have created a Unioin query but I need some help to specify "Where". I
want it to join all of PICSSHIP GSP if the dates are greater then the max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP HISTORY];

That where line doesn't work.

 

[Douglas J. Steele]

I can't think of any reason why you wouldn't be able to import an Union
query. I just tested, and I was able to import a Union query from Access 97
into Excel 2003.

How are you trying to do it? What error do you get? What versions of Access
and Excel are you using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doh! Union queries cannot be IMPORTED in when I am in excel. Is there a
query
that will combine these 2 data sources and allow me to IMPORT them in from
excel.

You sure you need a UNION query? Union queries don't know anything about
the
various tables within the subselects, which means that you cannot compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP Shipped
Date has embedded spaces in it, it must be enclosed in square brackets
(like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM [GSP
HISTORY])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI,

I have created a Unioin query but I need some help to specify "Where".
I
want it to join all of PICSSHIP GSP if the dates are greater then the
max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP HISTORY];

That where line doesn't work.

 

[Schwimms]

I import by going into excel then going to open. I find the access database
and open it, it then gives me a list of files I can open. It isn't in this
list. After that I can refresh this list by hitting the refresh button.

Did you mean export?

I can't think of any reason why you wouldn't be able to import an Union
query. I just tested, and I was able to import a Union query from Access 97
into Excel 2003.

How are you trying to do it? What error do you get? What versions of Access
and Excel are you using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doh! Union queries cannot be IMPORTED in when I am in excel. Is there a
query
that will combine these 2 data sources and allow me to IMPORT them in from
excel.

You sure you need a UNION query? Union queries don't know anything about
the
various tables within the subselects, which means that you cannot compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP Shipped
Date has embedded spaces in it, it must be enclosed in square brackets
(like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM [GSP
HISTORY])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

I have created a Unioin query but I need some help to specify "Where".
I
want it to join all of PICSSHIP GSP if the dates are greater then the
max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP HISTORY];

That where line doesn't work.

 

[Douglas J. Steele]

That's odd. I see the same thing when I try to import that way.

Try using the Import External Data option under the Data menu.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I import by going into excel then going to open. I find the access database
and open it, it then gives me a list of files I can open. It isn't in this
list. After that I can refresh this list by hitting the refresh button.

Did you mean export?

I can't think of any reason why you wouldn't be able to import an Union
query. I just tested, and I was able to import a Union query from Access
97
into Excel 2003.

How are you trying to do it? What error do you get? What versions of
Access
and Excel are you using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doh! Union queries cannot be IMPORTED in when I am in excel. Is there a
query
that will combine these 2 data sources and allow me to IMPORT them in
from
excel.

:

You sure you need a UNION query? Union queries don't know anything
about
the
various tables within the subselects, which means that you cannot
compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP
Shipped
Date has embedded spaces in it, it must be enclosed in square brackets
(like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM [GSP
HISTORY])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

I have created a Unioin query but I need some help to specify
"Where".
I
want it to join all of PICSSHIP GSP if the dates are greater then
the
max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP
HISTORY];

That where line doesn't work.

 

[Schwimms]

Tried it. Hrmm. I am going to start another Post. Maybe it is an add - in
that I am missing??

That's odd. I see the same thing when I try to import that way.

Try using the Import External Data option under the Data menu.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I import by going into excel then going to open. I find the access database
and open it, it then gives me a list of files I can open. It isn't in this
list. After that I can refresh this list by hitting the refresh button.

Did you mean export?

I can't think of any reason why you wouldn't be able to import an Union
query. I just tested, and I was able to import a Union query from Access
97
into Excel 2003.

How are you trying to do it? What error do you get? What versions of
Access
and Excel are you using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doh! Union queries cannot be IMPORTED in when I am in excel. Is there a
query
that will combine these 2 data sources and allow me to IMPORT them in
from
excel.

:

You sure you need a UNION query? Union queries don't know anything
about
the
various tables within the subselects, which means that you cannot
compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP
Shipped
Date has embedded spaces in it, it must be enclosed in square brackets
(like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM [GSP
HISTORY])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

I have created a Unioin query but I need some help to specify
"Where".
I
want it to join all of PICSSHIP GSP if the dates are greater then
the
max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP
HISTORY];

That where line doesn't work.

 

[Douglas J. Steele]

You still didn't say what version of Access, nor what version of Excel.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Tried it. Hrmm. I am going to start another Post. Maybe it is an add - in
that I am missing??

That's odd. I see the same thing when I try to import that way.

Try using the Import External Data option under the Data menu.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I import by going into excel then going to open. I find the access
database
and open it, it then gives me a list of files I can open. It isn't in
this
list. After that I can refresh this list by hitting the refresh button.

Did you mean export?

:

I can't think of any reason why you wouldn't be able to import an
Union
query. I just tested, and I was able to import a Union query from
Access
97
into Excel 2003.

How are you trying to do it? What error do you get? What versions of
Access
and Excel are you using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doh! Union queries cannot be IMPORTED in when I am in excel. Is
there a
query
that will combine these 2 data sources and allow me to IMPORT them
in
from
excel.

:

You sure you need a UNION query? Union queries don't know anything
about
the
various tables within the subselects, which means that you cannot
compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP
Shipped
Date has embedded spaces in it, it must be enclosed in square
brackets
(like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM
[GSP
HISTORY])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

I have created a Unioin query but I need some help to specify
"Where".
I
want it to join all of PICSSHIP GSP if the dates are greater then
the
max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP
HISTORY];

That where line doesn't work.

 

[Schwimms]

Doug,

Sorry, I did get it to work. I created the Union Query then created another
query off of the union. Now it imports in, in excel.

Life is good, thanks for your help!!

You still didn't say what version of Access, nor what version of Excel.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Tried it. Hrmm. I am going to start another Post. Maybe it is an add - in
that I am missing??

That's odd. I see the same thing when I try to import that way.

Try using the Import External Data option under the Data menu.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I import by going into excel then going to open. I find the access
database
and open it, it then gives me a list of files I can open. It isn't in
this
list. After that I can refresh this list by hitting the refresh button.

Did you mean export?

:

I can't think of any reason why you wouldn't be able to import an
Union
query. I just tested, and I was able to import a Union query from
Access
97
into Excel 2003.

How are you trying to do it? What error do you get? What versions of
Access
and Excel are you using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doh! Union queries cannot be IMPORTED in when I am in excel. Is
there a
query
that will combine these 2 data sources and allow me to IMPORT them
in
from
excel.

:

You sure you need a UNION query? Union queries don't know anything
about
the
various tables within the subselects, which means that you cannot
compare
values between the two tables.

Not only that, but the ALL in SELECT ALL is incorrect, since GSP
Shipped
Date has embedded spaces in it, it must be enclosed in square
brackets
(like
the table names), and the syntax is [TableName].[FieldName]

I think what you want is

SELECT *
FROM [PICSSHIP GSP]
WHERE [GSP Shipped Date] > (SELECT MAX([GSP Shipped Date]) FROM
[GSP
HISTORY])

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


HI,

I have created a Unioin query but I need some help to specify
"Where".
I
want it to join all of PICSSHIP GSP if the dates are greater then
the
max
date in the GSP HISTORY.

I have this so far:

SELECT ALL*
FROM [PICSSHIP GSP]
UNION ALL
SELECT ALL*
FROM [GSP HISTORY]
WHERE GSP Shipped Date [PICSSHIP GSP] > GSP Shipped Date [GSP
HISTORY];

That where line doesn't work.