UNUION ALL questions

P

peashoe

I have the following query:
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Packages.Price,
Packages.Description
FROM Packages INNER JOIN (Registrations INNER JOIN Attendees ON
Registrations.ID=Attendees.[School Or Organization ID]) ON
Packages.[Package Code]=Attendees.[Package Code]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
)
UNION ALL
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Transactions.Price,
Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
);

The two fields I really needed in the second part is Transactions.Price
and Transactions.Description but because the number of columns need to
match, I had to add the rest. Now, when I run the report, the value
pop-up box appears for the top items (Extended Days to Plus Price) How
do I get rid of this?

Thanks in advance
Lisa
 
K

KARL DEWEY

Try this ---

SELECT NULL AS [Extended Days], NULL AS [Extra Room Price],
NULL AS [Plus Price], Transactions.Price, Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or Organization])=Forms!Registrations![School
Or Organization])));
 
A

aaron.kempf

dont ever put spaces in any field, name or control name!



KARL said:
Try this ---

SELECT NULL AS [Extended Days], NULL AS [Extra Room Price],
NULL AS [Plus Price], Transactions.Price, Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or Organization])=Forms!Registrations![School
Or Organization])));


[email protected] said:
I have the following query:
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Packages.Price,
Packages.Description
FROM Packages INNER JOIN (Registrations INNER JOIN Attendees ON
Registrations.ID=Attendees.[School Or Organization ID]) ON
Packages.[Package Code]=Attendees.[Package Code]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
)
UNION ALL
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Transactions.Price,
Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
);

The two fields I really needed in the second part is Transactions.Price
and Transactions.Description but because the number of columns need to
match, I had to add the rest. Now, when I run the report, the value
pop-up box appears for the top items (Extended Days to Plus Price) How
do I get rid of this?

Thanks in advance
Lisa
Click to expand...
Click to expand...
 
P

peashoe

Karl,
I get " The select statement includes a reserved work or argument name
that is misspelled or missing, or the punctuation is incorrect.

I used:
SELECT NULL AS Attendees.[Extended Days],
NULL AS Packages.[Extra Room Price],
NULL AS Packages.[Plus Price]

any more ideas?
~L~


KARL said:
Try this ---

SELECT NULL AS [Extended Days], NULL AS [Extra Room Price],
NULL AS [Plus Price], Transactions.Price, Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or Organization])=Forms!Registrations![School
Or Organization])));


[email protected] said:
I have the following query:
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Packages.Price,
Packages.Description
FROM Packages INNER JOIN (Registrations INNER JOIN Attendees ON
Registrations.ID=Attendees.[School Or Organization ID]) ON
Packages.[Package Code]=Attendees.[Package Code]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
)
UNION ALL
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Transactions.Price,
Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
);

The two fields I really needed in the second part is Transactions.Price
and Transactions.Description but because the number of columns need to
match, I had to add the rest. Now, when I run the report, the value
pop-up box appears for the top items (Extended Days to Plus Price) How
do I get rid of this?

Thanks in advance
Lisa
Click to expand...
Click to expand...
 
P

peashoe

i agree - but I didnt build the database
~L~

dont ever put spaces in any field, name or control name!



KARL said:
Try this ---

SELECT NULL AS [Extended Days], NULL AS [Extra Room Price],
NULL AS [Plus Price], Transactions.Price, Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or Organization])=Forms!Registrations![School
Or Organization])));


[email protected] said:
I have the following query:
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Packages.Price,
Packages.Description
FROM Packages INNER JOIN (Registrations INNER JOIN Attendees ON
Registrations.ID=Attendees.[School Or Organization ID]) ON
Packages.[Package Code]=Attendees.[Package Code]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
)
UNION ALL
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Transactions.Price,
Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
);

The two fields I really needed in the second part is Transactions.Price
and Transactions.Description but because the number of columns need to
match, I had to add the rest. Now, when I run the report, the value
pop-up box appears for the top items (Extended Days to Plus Price) How
do I get rid of this?

Thanks in advance
Lisa
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

What you posted back is not what I said to do.
Use --
SELECT NULL AS [Extended Days], NULL AS [Extra Room Price], NULL AS [Plus
Price]


[email protected] said:
Karl,
I get " The select statement includes a reserved work or argument name
that is misspelled or missing, or the punctuation is incorrect.

I used:
SELECT NULL AS Attendees.[Extended Days],
NULL AS Packages.[Extra Room Price],
NULL AS Packages.[Plus Price]

any more ideas?
~L~


KARL said:
Try this ---

SELECT NULL AS [Extended Days], NULL AS [Extra Room Price],
NULL AS [Plus Price], Transactions.Price, Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or Organization])=Forms!Registrations![School
Or Organization])));


[email protected] said:
I have the following query:
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Packages.Price,
Packages.Description
FROM Packages INNER JOIN (Registrations INNER JOIN Attendees ON
Registrations.ID=Attendees.[School Or Organization ID]) ON
Packages.[Package Code]=Attendees.[Package Code]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
)
UNION ALL
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Transactions.Price,
Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
);

The two fields I really needed in the second part is Transactions.Price
and Transactions.Description but because the number of columns need to
match, I had to add the rest. Now, when I run the report, the value
pop-up box appears for the top items (Extended Days to Plus Price) How
do I get rid of this?

Thanks in advance
Lisa
Click to expand...
Click to expand...
Click to expand...
 
P

peashoe

sorry Karl I didn 't see that I had to remove the tables - it works
THANK YOU! But now, something else. My totals for those rows will not
work in the report with NULL fields, is there a way to set these fields
to zeros instead of NULL?

~L~


KARL said:
What you posted back is not what I said to do.
Use --
SELECT NULL AS [Extended Days], NULL AS [Extra Room Price], NULL AS [Plus
Price]


[email protected] said:
Karl,
I get " The select statement includes a reserved work or argument name
that is misspelled or missing, or the punctuation is incorrect.

I used:
SELECT NULL AS Attendees.[Extended Days],
NULL AS Packages.[Extra Room Price],
NULL AS Packages.[Plus Price]

any more ideas?
~L~


KARL said:
Try this ---

SELECT NULL AS [Extended Days], NULL AS [Extra Room Price],
NULL AS [Plus Price], Transactions.Price, Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or Organization])=Forms!Registrations![School
Or Organization])));


:

I have the following query:
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Packages.Price,
Packages.Description
FROM Packages INNER JOIN (Registrations INNER JOIN Attendees ON
Registrations.ID=Attendees.[School Or Organization ID]) ON
Packages.[Package Code]=Attendees.[Package Code]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
)
UNION ALL
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Transactions.Price,
Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
);

The two fields I really needed in the second part is Transactions.Price
and Transactions.Description but because the number of columns need to
match, I had to add the rest. Now, when I run the report, the value
pop-up box appears for the top items (Extended Days to Plus Price) How
do I get rid of this?

Thanks in advance
Lisa
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

SELECT 0 AS [Extended Days], 0 AS [Extra Room Price], 0 AS [Plus Price] ...

In actual fact, since that's the second subselect, you don't even need the
field names: you could simply use SELECT 0, 0, 0...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


sorry Karl I didn 't see that I had to remove the tables - it works
THANK YOU! But now, something else. My totals for those rows will not
work in the report with NULL fields, is there a way to set these fields
to zeros instead of NULL?

~L~


KARL said:
What you posted back is not what I said to do.
Use --
SELECT NULL AS [Extended Days], NULL AS [Extra Room Price], NULL AS [Plus
Price]


[email protected] said:
Karl,
I get " The select statement includes a reserved work or argument name
that is misspelled or missing, or the punctuation is incorrect.

I used:
SELECT NULL AS Attendees.[Extended Days],
NULL AS Packages.[Extra Room Price],
NULL AS Packages.[Plus Price]

any more ideas?
~L~


KARL DEWEY wrote:
Try this ---

SELECT NULL AS [Extended Days], NULL AS [Extra Room Price],
NULL AS [Plus Price], Transactions.Price, Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School
Or Organization])));


:

I have the following query:
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Packages.Price,
Packages.Description
FROM Packages INNER JOIN (Registrations INNER JOIN Attendees ON
Registrations.ID=Attendees.[School Or Organization ID]) ON
Packages.[Package Code]=Attendees.[Package Code]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
)
UNION ALL
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Transactions.Price,
Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
);

The two fields I really needed in the second part is
Transactions.Price
and Transactions.Description but because the number of columns need
to
match, I had to add the rest. Now, when I run the report, the value
pop-up box appears for the top items (Extended Days to Plus Price)
How
do I get rid of this?

Thanks in advance
Lisa
Click to expand...
Click to expand...
Click to expand...
 
P

peashoe

PERFECT!!!
thanks guys

~L~
SELECT 0 AS [Extended Days], 0 AS [Extra Room Price], 0 AS [Plus Price] ...

In actual fact, since that's the second subselect, you don't even need the
field names: you could simply use SELECT 0, 0, 0...


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


sorry Karl I didn 't see that I had to remove the tables - it works
THANK YOU! But now, something else. My totals for those rows will not
work in the report with NULL fields, is there a way to set these fields
to zeros instead of NULL?

~L~


KARL said:
What you posted back is not what I said to do.
Use --
SELECT NULL AS [Extended Days], NULL AS [Extra Room Price], NULL AS [Plus
Price]


:

Karl,
I get " The select statement includes a reserved work or argument name
that is misspelled or missing, or the punctuation is incorrect.

I used:
SELECT NULL AS Attendees.[Extended Days],
NULL AS Packages.[Extra Room Price],
NULL AS Packages.[Plus Price]

any more ideas?
~L~


KARL DEWEY wrote:
Try this ---

SELECT NULL AS [Extended Days], NULL AS [Extra Room Price],
NULL AS [Plus Price], Transactions.Price, Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School
Or Organization])));


:

I have the following query:
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Packages.Price,
Packages.Description
FROM Packages INNER JOIN (Registrations INNER JOIN Attendees ON
Registrations.ID=Attendees.[School Or Organization ID]) ON
Packages.[Package Code]=Attendees.[Package Code]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
)
UNION ALL
(
SELECT Attendees.[Extended Days],
Packages.[Extra Room Price],
Packages.[Plus Price],
Transactions.Price,
Transactions.Description
FROM Registrations INNER JOIN Transactions ON Registrations.ID =
Transactions.[School Or Organization ID]
WHERE (((Registrations.[School Or
Organization])=Forms!Registrations![School Or Organization]))
);

The two fields I really needed in the second part is
Transactions.Price
and Transactions.Description but because the number of columns need
to
match, I had to add the rest. Now, when I run the report, the value
pop-up box appears for the top items (Extended Days to Plus Price)
How
do I get rid of this?

Thanks in advance
Lisa
Click to expand...
Click to expand...
Click to expand...
 

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

Adding field from seperate tables in a Report 1
DoCmd.OpenQuery or DLookup? 1
Insert Into Syntax error 8
DSum question 6
Refresh combo query 3
can't use an aggregate function in an expression? 5
Help setting up Ms-Access Database 3
Blank Fields in Report 2

Top