Help with a Union Query

[Bear]

Hey all,

Thanks so much for your help in the past. I really do try to search "help" for the answer to my problems but you seem to be the only ones that can help me.

I am still working on my home library. I have incorporated quite a few bells and whistles and am learning more every day. My major problem is that every time I add a "KooL" feature I think up several more to add.

Here is the current "shiney":

When reading a series I sometimes don't know/forget which title will come next in the series. Sure, I could pick up several books in the series and spend time flipping through pages but I wanted something a little quicker and easier. Sooooo...

I added two columns to my library database: "Series", and "Series Order". I have spent time flipping through pages (hopefully, one time only) and have set up these two columns with the name of the series and the order in which the title will fall in the series. The query for each series chooses the name of the series and the series order comes with it. All I have to do is sort and, Wham-Bama-Lama, I have what I want. No problem so far...

However, I ran into something that has me scratching my head. When a short story is part of a series I can't just write a query to include all of the books 'cause the short story will be left out. So I figured I could write a Union Query that would get what I wanted (The Series is "Jig the Goblin"):

SELECT Title, Series Order, “Book” AS Genre FROM [Books] WHERE [Series]=”Jig the Goblin”
UNION SELECT Title, Series Order, “Short Stories” FROM [Short Stories] WHERE [Series]=”Jig the Goblin”


Books = Table


Short Story = Table
Title = Column
Series = Column
Series Order = Column


I have spent hours trying to correct error messages that keep popping up. These messages include everything from "can't find" objects to syntax errors to "enter parameter value". (I hate that one!) I have removed brackets and put them somewhere else, removed quotes and put them somewhere else, checked spelling, removed and added punctuation, etc., etc., etc...

The thing that puzzles me the most is that I have written Union Queries before that are almost exactly like this one. I have never had these problems before and I am very frustrated.

Any help or advice would be greatly appreciated

[pbaldy]

For starters, Series Order would need to be bracketed because of the inadvisable space. You say the name of the table is Short Story but have Short Stories in the SQL. Make sure that's correct.

[Bear]

Sorry, just tried bracketing "Series Order" like you said and it did not work. Error message I got was:

The Microsoft Access database engine could not find the object’[Series]=”Jig the Goblin”. Make sure the object exists and that you spell its name and the path name correctly.

I do not understand. If your suggestion had worked - why wouldn't I have to bracket "Title" since it is also a column set up as "Text: just like the "Series Order" column?

I do not understand. This union query, almost exactly like I have it now, has worked before. All I did was change a few names this time. ie. - Series Order, Series, and Jig the Goblin.

[Bear]

What do you mean by "inadvisable space"?

[Bear]

You say the name of the table is Short Story but have Short Stories in the SQL. Make sure that's correct.

Ooops! Guess I copied it wrong. The table is "Short Stories".

The SQL will create a column named "Genre" and "Short Story" will be in the "Genre" column in the correct place.

[pbaldy]

That field has to be bracketed because of the space. Spaces aren't worth the trouble in the long run, as you always have to bracket them. I use capitalization: SeriesOrder.

The error implies that Series isn't the name of the field, though because it's also including the value perhaps you just need spaces. Try

SELECT Title, [Series Order], “Book” AS Genre FROM [Books] WHERE [Series] = ”Jig the Goblin”
UNION SELECT Title, [Series Order], “Short Stories” FROM [Short Stories] WHERE [Series] = ”Jig the Goblin”

If that doesn't work, can you post the db here?

[Bear]

Okay, now I understand about the space (I think). Since I have spaces in much of the data and it worked fine for the data I thought that it didn't matter about the column headings.

Now let me get this straight. It didn't matter for the data because the columns are "set up" for text - but the column headings are a different story altogether. Right?

I tried what you suggested and it didn't work - same error message.

I have been working with computers in one capacity or another for quite a long time. However, I am fairly new to Access. I am still learning some of the methods and jargon. Does db stand for database? Are you asking me to post the entire database? I am fairly hesitant to do so for three reasons:

1. Although I am not finished entering all of my books there are presently 737 books listed plus several hundred queries and a lot of tables and macros. That's a lot of "stuff"!

2. As I am not an expert with Access I have done a few things that may not be kosher to make some things work. They work but they are not pretty. I am a little ashamed to have someone with "Knowledge" see what I have done.

3. I don't know how.

[Bear]

Just went back and read your reply again. Didn't know what you meant by "capitalization" but finally figured it out. The reason I used spaces was to make everything easier to read/use. I am not the only one that may end up using this database (young grandchildren and others).

[pbaldy]

1) If you can post those two tables, it would allow me to sort out this problem.

2) I guarantee you, whatever you've got we've seen worse.

3) In the Post Reply or Go Advanced area there's a Manage Attachments button.

[ssanfu]

PMFJI,

Okay, now I understand about the space (I think). Since I have spaces in much of the data and it worked fine for the data I thought that it didn't matter about the column headings.

Spaces in data are fine, but there are reasons why you shouldn't use spaces (or special characters) in object names. Only use letters, numbers and the underscore.
Object names are the names of tables, queries, forms, reports, fields and modules.

You have to bracket names with spaces. It might work in MS Access, but if you ever decide to convert to SQL Express/SQL Server/MySQL/etc, you would have to do a rewrite because they don't accept spaces in names (AFAIK).

Some of the special characters (the shifted numbers on the keyboard) are delimiters (it is tempting, but do not use # in field names). The space character is also a delimiter, which is why you have the bracket names with spaces.

No one should see the object names (except the developer) because everything should be through the use of forms, and reports. You can change labels and captions to whatever you want on the form/report.

--- examples:

"SeriesOrder" (aka Camel back)
"Series_Order" (the underscore)

"DateThatTheSeriesBegan"
"Date_that_the_Series_Began"

You can still read the name easily, even without spaces in the name, but you don't have to bracket the name.

[Bear]

Wow! (and I do mean WOW!!!)

Been doing a little thinking here. That means I need to go back and make some major changes to my database. That means lots and lots of work. Whew!

It also means that I need to figure out how to use forms and reports. I had never felt a need for them but I guess you are right. I not only need to set up the forms and reports in order to give me the information I need but I need to configure the database so that I can do so. It may mean that I need to start over from scratch.

[Bear]

One reason that I started teaching myself Access was for my job. I can see now that I will, also, need to know about forms and reports. I am rethinking this situation.

I have more than six months of work tied up in this library database. I have decided that I want it to be correct and I want to know about forms and reports. So...

I have decided to start all over from scratch and build my database like I should have from the beginning. It may take several months to get back to this point. Also, I may find that, once everything is set up as it should be, I will not have the problem that I originally brought forth in this thread. If I do then I will be back asking the same question.

Until then, thank you so much for your help. I feel guilty about wasting your time. I did, however, learn something and that is why I started this in the first place - to learn.

Again, thank you.

[pbaldy]

No time was wasted. Post back with any questions as you move forward.