Making Duplicate Query Results On Purpose

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know that people normally try to eliminate duplicates and such, but I need
to do the opposite.

I have this simple ficticious query (Query1) that pulls all records from the
ficticious table (Table1).

Query1: SELECT * FROM [Table1];

The results are something like:

A
B
C
D

How would I change the query to give the following results?

A
A
B
B
C
C
D
D

Thanks,

Nick
 
Try using Onion All query with order by the field name

SELECT *
FROM Table1 Union All
SELECT *
FROM Table1
Order By FieldName
 
So close! It's almost there. Results are in the wrong order though. It's
giving me this:

A
B
C
D
A
B
C
D

Instead of:

A
A
B
B
C
C
D
D

I've been fiddling with the order by statement, but I'm not having much
success. Suggestions?

Thank you!

Nick

Ofer said:
Try using Onion All query with order by the field name

SELECT *
FROM Table1 Union All
SELECT *
FROM Table1
Order By FieldName

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
I know that people normally try to eliminate duplicates and such, but I need
to do the opposite.

I have this simple ficticious query (Query1) that pulls all records from the
ficticious table (Table1).

Query1: SELECT * FROM [Table1];

The results are something like:

A
B
C
D

How would I change the query to give the following results?

A
A
B
B
C
C
D
D

Thanks,

Nick
Click to expand...
Click to expand...
 
Tatakau said:
I know that people normally try to eliminate duplicates and such, but I need
to do the opposite.

I have this simple ficticious query (Query1) that pulls all records from the
ficticious table (Table1).

Query1: SELECT * FROM [Table1];

The results are something like:

A
B
C
D

How would I change the query to give the following results?

A
A
B
B
C
C
D
D
Click to expand...


SELECT * FROM [Table1]
UNION ALL
SELECT * FROM [Table1]
ORDER BY somefield
 
Can you post the SQL?

And the name of the field you need to sort by

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
So close! It's almost there. Results are in the wrong order though. It's
giving me this:

A
B
C
D
A
B
C
D

Instead of:

A
A
B
B
C
C
D
D

I've been fiddling with the order by statement, but I'm not having much
success. Suggestions?

Thank you!

Nick

Ofer said:
Try using Onion All query with order by the field name

SELECT *
FROM Table1 Union All
SELECT *
FROM Table1
Order By FieldName

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
I know that people normally try to eliminate duplicates and such, but I need
to do the opposite.

I have this simple ficticious query (Query1) that pulls all records from the
ficticious table (Table1).

Query1: SELECT * FROM [Table1];

The results are something like:

A
B
C
D

How would I change the query to give the following results?

A
A
B
B
C
C
D
D

Thanks,

Nick
Click to expand...
Click to expand...
Click to expand...
 
I think I sorta got a juryrigged solution using a second query. I made a
Query2 that selected everything from Query1 (Query1 was using the Union All)
and then had Query2 sort by the field I wanted.

Sort of a bother to have to use 2 queries for it though... if I can get it
down to 1, that would probably work better.

Beware: This SQL is very ugly.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
START SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thank you for helping me out with this!

Nick

Ofer said:
Can you post the SQL?

And the name of the field you need to sort by

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
So close! It's almost there. Results are in the wrong order though. It's
giving me this:

A
B
C
D
A
B
C
D

Instead of:

A
A
B
B
C
C
D
D

I've been fiddling with the order by statement, but I'm not having much
success. Suggestions?

Thank you!

Nick

Ofer said:
Try using Onion All query with order by the field name

SELECT *
FROM Table1 Union All
SELECT *
FROM Table1
Order By FieldName

--
\\// Live Long and Prosper \\//
BS"D


:

I know that people normally try to eliminate duplicates and such, but I need
to do the opposite.

I have this simple ficticious query (Query1) that pulls all records from the
ficticious table (Table1).

Query1: SELECT * FROM [Table1];

The results are something like:

A
B
C
D

How would I change the query to give the following results?

A
A
B
B
C
C
D
D

Thanks,

Nick
Click to expand...
Click to expand...
Click to expand...
 
Move the Order By to the End of the query

SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));SELECT tour.tournum AS [Tour Number],
salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
I think I sorta got a juryrigged solution using a second query. I made a
Query2 that selected everything from Query1 (Query1 was using the Union All)
and then had Query2 sort by the field I wanted.

Sort of a bother to have to use 2 queries for it though... if I can get it
down to 1, that would probably work better.

Beware: This SQL is very ugly.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
START SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thank you for helping me out with this!

Nick

Ofer said:
Can you post the SQL?

And the name of the field you need to sort by

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
So close! It's almost there. Results are in the wrong order though. It's
giving me this:

A
B
C
D
A
B
C
D

Instead of:

A
A
B
B
C
C
D
D

I've been fiddling with the order by statement, but I'm not having much
success. Suggestions?

Thank you!

Nick

:

Try using Onion All query with order by the field name

SELECT *
FROM Table1 Union All
SELECT *
FROM Table1
Order By FieldName

--
\\// Live Long and Prosper \\//
BS"D


:

I know that people normally try to eliminate duplicates and such, but I need
to do the opposite.

I have this simple ficticious query (Query1) that pulls all records from the
ficticious table (Table1).

Query1: SELECT * FROM [Table1];

The results are something like:

A
B
C
D

How would I change the query to give the following results?

A
A
B
B
C
C
D
D

Thanks,

Nick
Click to expand...
Click to expand...
Click to expand...
 
It won't work, and says that the Order By can only be used in the first part
of the query.

Nick

Ofer said:
Move the Order By to the End of the query

SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));SELECT tour.tournum AS [Tour Number],
salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
I think I sorta got a juryrigged solution using a second query. I made a
Query2 that selected everything from Query1 (Query1 was using the Union All)
and then had Query2 sort by the field I wanted.

Sort of a bother to have to use 2 queries for it though... if I can get it
down to 1, that would probably work better.

Beware: This SQL is very ugly.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
START SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thank you for helping me out with this!

Nick

Ofer said:
Can you post the SQL?

And the name of the field you need to sort by

--
\\// Live Long and Prosper \\//
BS"D


:

So close! It's almost there. Results are in the wrong order though. It's
giving me this:

A
B
C
D
A
B
C
D

Instead of:

A
A
B
B
C
C
D
D

I've been fiddling with the order by statement, but I'm not having much
success. Suggestions?

Thank you!

Nick

:

Try using Onion All query with order by the field name

SELECT *
FROM Table1 Union All
SELECT *
FROM Table1
Order By FieldName

--
\\// Live Long and Prosper \\//
BS"D


:

I know that people normally try to eliminate duplicates and such, but I need
to do the opposite.

I have this simple ficticious query (Query1) that pulls all records from the
ficticious table (Table1).

Query1: SELECT * FROM [Table1];

The results are something like:

A
B
C
D

How would I change the query to give the following results?

A
A
B
B
C
C
D
D

Thanks,

Nick
Click to expand...
Click to expand...
Click to expand...
 
I just noticed that you used the Union All twice, try and remove the order by
from the first section also

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
It won't work, and says that the Order By can only be used in the first part
of the query.

Nick

Ofer said:
Move the Order By to the End of the query

SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));SELECT tour.tournum AS [Tour Number],
salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
I think I sorta got a juryrigged solution using a second query. I made a
Query2 that selected everything from Query1 (Query1 was using the Union All)
and then had Query2 sort by the field I wanted.

Sort of a bother to have to use 2 queries for it though... if I can get it
down to 1, that would probably work better.

Beware: This SQL is very ugly.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
START SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thank you for helping me out with this!

Nick

:

Can you post the SQL?

And the name of the field you need to sort by

--
\\// Live Long and Prosper \\//
BS"D


:

So close! It's almost there. Results are in the wrong order though. It's
giving me this:

A
B
C
D
A
B
C
D

Instead of:

A
A
B
B
C
C
D
D

I've been fiddling with the order by statement, but I'm not having much
success. Suggestions?

Thank you!

Nick

:

Try using Onion All query with order by the field name

SELECT *
FROM Table1 Union All
SELECT *
FROM Table1
Order By FieldName

--
\\// Live Long and Prosper \\//
BS"D


:

I know that people normally try to eliminate duplicates and such, but I need
to do the opposite.

I have this simple ficticious query (Query1) that pulls all records from the
ficticious table (Table1).

Query1: SELECT * FROM [Table1];

The results are something like:

A
B
C
D

How would I change the query to give the following results?

A
A
B
B
C
C
D
D

Thanks,

Nick
Click to expand...
Click to expand...
Click to expand...
 
If you want to sort in a union query, you must have the columns in the
SELECT Clause and you must sort by the names of the columns in the first
query, but put the sort at the end of the last query.


SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 <<<
These columns are not in the select clause >>> and this order by is ignored.

==========================================================================
If you want to order by these columns you need to add them to all three of
your subqueries in the union query.
==========================================================================

UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));SELECT tour.tournum AS [Tour Number],
salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))

UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging = tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 <<< These
columns are not in the select clause of the first query >>>


Ofer said:
Move the Order By to the End of the query

SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure
Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging =
tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 UNION
ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure
Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging =
tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));SELECT tour.tournum AS [Tour Number],
salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure
Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging =
tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
UNION ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure
Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging =
tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1

--
\\// Live Long and Prosper \\//
BS"D


Tatakau said:
I think I sorta got a juryrigged solution using a second query. I made a
Query2 that selected everything from Query1 (Query1 was using the Union
All)
and then had Query2 sort by the field I wanted.

Sort of a bother to have to use 2 queries for it though... if I can get
it
down to 1, that would probably work better.

Beware: This SQL is very ugly.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
START SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT tour.tournum AS [Tour Number], salutation.salutation AS
Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure
Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging =
tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False))
ORDER BY tour.resdatearrive, tbl_building.CheckIn, contacts.last1 UNION
ALL
SELECT tour.tournum AS [Tour Number], salutation.salutation AS
Salutation,
"Expires " & Format([resdatedepart],"mmmm d"", ""yyyy") AS [Departure
Date],
tbl_building.CheckIn AS Building, "Vacation Package Discovery Guests" AS
Card1, "V.I.P. Discount Card" AS Card2, "THIS CARD DOES NOT AUTHORIZE
CHARGES
TO GUESTROOM ACCOUNTS." AS Card3
FROM tbl_wave RIGHT JOIN (tbl_building RIGHT JOIN ((contacts LEFT JOIN
salutation ON contacts.leadnum = salutation.leadnum) RIGHT JOIN tour ON
contacts.leadnum = tour.leadnum) ON tbl_building.tblLodging =
tour.building)
ON tbl_wave.wave = tour.tourwave
WHERE ((([tour].[lettertype]<>3 And
IIf(0<>DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tournum])=DLookUp("[printtour]","_local","[index] = " &
1),([tour].[tourstatus])="Pending Check-In Letter" And
([tour].[resdatearrive]) Between Date() And
Date()+DLookUp("[checkinrange]","_local","[index]=1") And
[tour].[keycode]<>"TRIAL"))<>False));

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
END SQL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thank you for helping me out with this!

Nick

Ofer said:
Can you post the SQL?

And the name of the field you need to sort by

--
\\// Live Long and Prosper \\//
BS"D


:

So close! It's almost there. Results are in the wrong order though.
It's
giving me this:

A
B
C
D
A
B
C
D

Instead of:

A
A
B
B
C
C
D
D

I've been fiddling with the order by statement, but I'm not having
much
success. Suggestions?

Thank you!

Nick

:

Try using Onion All query with order by the field name

SELECT *
FROM Table1 Union All
SELECT *
FROM Table1
Order By FieldName

--
\\// Live Long and Prosper \\//
BS"D


:

I know that people normally try to eliminate duplicates and such,
but I need
to do the opposite.

I have this simple ficticious query (Query1) that pulls all
records from the
ficticious table (Table1).

Query1: SELECT * FROM [Table1];

The results are something like:

A
B
C
D

How would I change the query to give the following results?

A
A
B
B
C
C
D
D

Thanks,

Nick
Click to expand...
Click to expand...
Click to expand...
 
Tatakau said:
So close! It's almost there. Results are in the wrong order though. It's
giving me this:

A
B
C
D
A
B
C
D

Instead of:

A
A
B
B
C
C
D
D
Click to expand...

Try this...

Select * from
(Select * from Table1
Union All
Select * from Table1) DuplicatedTable Order by your_desired_Columns
 
Back
Top