Skip a line

[Bear]

When writing a SQL expression, is there a command to skip a line?



Am I asking this correctly?

I have written a SQL expression that works fine. However, when the query comes up on my screen it is a long list and a little daunting. I would like a space or skipped line or empty row before it prints the next data.

I am still not sure if I am asking this right.

[pbaldy]

Most of us don't let users into tables or queries directly. If you use a form or report to present the query data to the user, you'll have much more control over everything, including the spacing between records.

[rpeare]

You can likely do this with a union query assuming you have a primary key field you can exploit. let's assume you have a table with two fields

tblTest
TestID
TestDesc

SELECT tblTest.TestID, tblTest.TestDesc
FROM tblTest
ORDER BY tblTest.TestID, TestDesc DESC
UNION ALL
SELECT tblTest.TestID, null as TestDesc
FROM BtblTest
ORDER BY tblTest.TestID, TestDesc DESC

this SQL will sort your data the way you want with an extra space

[Bear]

Most of us don't let users into tables or queries directly. If you use a form or report to present the query data to the user, you'll have much more control over everything, including the spacing between records.

I am trying to learn to use forms and reports so to do exactly that. However, I want to be able to use the query better myself.

[Bear]

You can likely do this with a union query assuming you have a primary key field you can exploit. let's assume you have a table with two fields

tblTest
TestID
TestDesc

SELECT tblTest.TestID, tblTest.TestDesc
FROM tblTest
ORDER BY tblTest.TestID, TestDesc DESC
UNION ALL
SELECT tblTest.TestID, null as TestDesc
FROM BtblTest
ORDER BY tblTest.TestID, TestDesc DESC

this SQL will sort your data the way you want with an extra space

Okay, I think I understand. The way that I read this is the space will fall between the two sections (where the "UNION ALL" command sits). I have a union query and wish the space to fall afterwards:

SELECT AuthorLast, AuthorFirst, Middle, Title, "Book" AS Genre FROM Books WHERE (((Left([AuthorLast],1))="A"))
ORDER BY AuthorLast
UNION SELECT AuthorLast, AuthorFirst, Middle, Title, "ShortStory" AS Genre FROM ShortStories WHERE (((Left([AuthorLast],1))="A"))
ORDER BY AuthorLast
UNION SELECT AuthorLast, AuthorFirst, Middle, Title, "Article" AS Genre FROM Articles WHERE (((Left([AuthorLast],1))="A"))
ORDER BY AuthorLast
UNION SELECT PoetLastName, PoetFirstName, Middle, Title, "Poem" AS Genre FROM Poems WHERE (((Left([PoetLastName],1))="A"))
ORDER BY AuthorLast;

Then a space.

[rpeare]

Well you have a problem then, because your database is not normalized. all of your genres should be in one table with a single field that describes the genre each item is in without that you can't use the method I suggested. Secondly if you don't have a primary key for each item in your database you can't really use the method I'm describing either.

[Bear]

...because your database is not normalized.

Okay, I'm lost. What is normalized?

[ssanfu]

Okay, I'm lost. What is normalized?

http://www.accessmvp.com/Strive4Peace/

See chapter 3

[rpeare]

The short answer is you've got four tables all tracking exactly the same information which is a very bad practice. All the tables with the same information should be on the same table with one additional field that helps you identify the genre.

[Bear]

The short answer is you've got four tables all tracking exactly the same information which is a very bad practice. All the tables with the same information should be on the same table with one additional field that helps you identify the genre.

Sorry, but no, I don't have four tables that track the same information. I have a table for books, a table for short stories, a table for articles, and a table for poems. Each has a primary key and I have built relationships based on the primary key. The information in each table is unique. The only duplication of information may be the title of books in which each are found (ie. short story, article, or poem) and that duplication does not happen often. I am not being defensive or trying to say that I know more than you (heaven forbid). I am simply explaining why I have done it in this manner.

Believe me, I really appreciate your concern and I am very happy that you are trying to correct what you perceive to be gross mistakes on my part. That is the way that I learn - by trial and error. However, I have been working on this (my home library) for a long, long time and have referred to every book and help section that I could find (including this forum). I have started from scratch six times because something was unworkable.

Yes, at one time I had all of the "genres" included in one table but found that it did not suit my purposes at all. I have streamlined my database(s) and trimmed off all extraneous "meat" until I came to the present incarnation and, so far, it works perfectly - with a couple of exceptions. I am trying to only address one problem at a time. The present problem (and it is a very minor one) is the fact that I would like to make my union query a little easier to read. I would like very much to add a space.

Okay, your method would not work for me. I understand that and do not question it at all. Is there another method that would Work? I cannot find a list of all SQL commands but feel sure that if I would there would be a command for "space" or something like it.

[rpeare]

Sorry, but no, I don't have four tables that track the same information

. I have a table for books, a table for short stories, a table for articles, and a table for poems. Each has a primary key and I have built relationships based on the primary key. The information in each table is unique. The only duplication of information may be the title of books in which each are found (ie. short story, article, or poem) and that duplication does not happen often. I am not being defensive or trying to say that I know more than you (heaven forbid). I am simply explaining why I have done it in this manner.

The data you listed in your post is identical, identical not in content, but in design

Author Last Name
Author First Name
Author Middle Initial
Title

For the purposes of database design, if you have four tables that are each tracking this exact information, the *structure* is identical and therefore your database is not normalized. Unless of course there are fields you are not telling us about in each of these genre tables, but likely anything else you are tracking can be done on a normalized table, without knowing the structure of your data beyond these fields it would be fruitless to suggest a way to continue.

If you have an author table with a primary key you might be able to use that, if you don't that's another stumbling block.

[Bear]

[FONT=Times New Roman][SIZE=3]

The data you listed in your post is identical, identical not in content, but in design

Author Last Name
Author First Name
Author Middle Initial
Title

For the purposes of database design, if you have four tables that are each tracking this exact information, the *structure* is identical and therefore your database is not normalized. Unless of course there are fields you are not telling us about in each of these genre tables, but likely anything else you are tracking can be done on a normalized table, without knowing the structure of your data beyond these fields it would be fruitless to suggest a way to continue.

If you have an author table with a primary key you might be able to use that, if you don't that's another stumbling block.

Okay, you caught me. Yes, there are more fields that I am not telling you about. I did not feel a need to go into lengthy explanations about something that was not related to my question. (It looks like I did anyway)

This has gone way, way, way beyond the simple yes or no question that I originally asked.

Forget it! I'll just live with my union query the way it is.

[rpeare]

Do you have an author table with a primary key?

If so you may be able to do something with this query.

[ssanfu]

Sorry, but no, I don't have four tables that track the same information. I have a table for books, a table for short stories, a table for articles, and a table for poems. Each has a primary key and I have built relationships based on the primary key. The information in each table is unique. The only duplication of information may be the title of books in which each are found (ie. short story, article, or poem) and that duplication does not happen often. I am not being defensive or trying to say that I know more than you (heaven forbid). I am simply explaining why I have done it in this manner.

And yet it doesn't sound like you have a properly normalized structure. As long as it is working for you, great. (How would you feel about posting a picture of the relationship window??)

<snip> I have started from scratch six times because something was unworkable.

Been there, done that...... still doing that (I need to design more on paper first....)

The present problem (and it is a very minor one) is the fact that I would like to make my union query a little easier to read. I would like very much to add a space.

A query just returns a recordset. Its not really designed to have formatting. That is what forms & reports are for.

Okay, your method would not work for me. I understand that and do not question it at all. Is there another method that would Work?

A properly normalized structure using forms and subforms or reports and subreports.

I cannot find a list of all SQL commands but feel sure that if I would there would be a command for "space" or something like it.

I have never heard of a "SQL" command to add a blank line in the recordset. But if you want to check yourself, try:

http://www.w3schools.com/sql/
http://office.microsoft.com/en-us/ac...006252688.aspx
http://www.fontstuff.com/access/acctut15.htm

Also, Google "msaccess sql" for a tone more sites...