not the right heading?

[junkmale]

i'm trying to display as you can see, all books and periodicals published by the same publisher in a certain year.

The code i placed below works, but one problem is this. The end result shows in this order:

"BookID Title PublisherName YearofPub"

BookID in this case is not quite the right heading for the column, as there are also periodicals in the list, and should actually read "BookID/PeriodicalID".

How can i make this possible? Or is there some way to go around this?

[Btw, i'm quite new to SQL and ACCESS as you can probably tell, so if anything in my code works but shouldn't be done that way, please let me know also... plenty thanks! ]


SELECT Books.BookID, Books.Title, Publisher.PublisherName, Books.YearofPub
FROM Publisher INNER JOIN Books ON Publisher.PublisherID = Books.Publisher
WHERE (((Publisher.PublisherName) Like [Enter Publisher Name]) AND ((Books.YearofPub) Like [Enter Year]));
UNION SELECT Periodical.PeriodicalID, Periodical.Title, Publisher.PublisherName, Periodical.YearPub
FROM Publisher INNER JOIN Periodical ON Publisher.PublisherID = Periodical.Publisher
WHERE (((Publisher.PublisherName) Like [Enter Publisher Name]) AND ((Periodical.YearPub) Like [Enter Year]));

 

[namliam]

SELECT Books.BookID as "BookID/Periodical,.....

Regards

 

[junkmale]

thanks!

 

[junkmale]

oh no, another related problem.

i've gone on to use the query to generate a report.

To my dismay, i realise i had used similar looking numbers as ID for both Books and Periodicals.

This gives me the problem below

Report: All publications by Publisher:MicroPublications

ID Title
1 The legend of Z
2 Tales of X
1 Warstreet journal

This creates confusion, as there is nothing to diffentiate a book from a periodical!!! And also, repeated IDs do not look good...

Any suggestions?

 

[MattS]

SELECT "B" & Books.BookID............
UNION SELECT "P" & Periodical.PeriodicalID........

This would give you B1, B2, P1, P2 etc........

Alternatively, add a field to the query:

SELECT "Book" As Type, Books.BookID............
UNION SELECT "Periodical", Periodical.PeriodicalID........

 

[namliam]

An id should be meaningless to a user, just dont show it....

Regards

 

[junkmale]

SELECT "B" & Books.BookID............
UNION SELECT "P" & Periodical.PeriodicalID........

This would give you B1, B2, P1, P2 etc........

Alternatively, add a field to the query:

SELECT "Book" As Type, Books.BookID............
UNION SELECT "Periodical", Periodical.PeriodicalID........

That worked great!

An id should be meaningless to a user, just dont show it....

Regards

hmm, yah, you're right.... the user probably doesn't need the ID,

but i still need to differentiate the books from the periodicals...



THANKS!