hymnal db sql coding

[mikevds]

I have a hymnal DB I need some help.

I am trying to set up a list box two of them with name of song for one and hymnal for the other.

This is the sql coding
SELECT songsoncdandhymnalapr12007.ID, songsoncdandhymnalapr12007.NameofSong, songsoncdandhymnalapr12007.hymnalyes, songsoncdandhymnalapr12007.[hymnal/cdname], songsoncdandhymnalapr12007.MEdiatype, songsoncdandhymnalapr12007.hymnalyes, songsoncdandhymnalapr12007.cdno, songsoncdandhymnalapr12007.composernum, songsoncdandhymnalapr12007.[Composer name], songsoncdandhymnalapr12007.ArrangerNAme, songsoncdandhymnalapr12007.ArrangerNumber, songsoncdandhymnalapr12007.authornumber, songsoncdandhymnalapr12007.AuthorandKey, songsoncdandhymnalapr12007.Scripture, songsoncdandhymnalapr12007.key, songsoncdandhymnalapr12007.Tune
FROM songsoncdandhymnalapr12007;

however the coding it don' like and all I need is a query to go from name of song select to name of Hymnal.
Can anyone help me out on this mess?

I can read books and books about it to a certain extent it explains what a union query is People don't realize it's no use if their is no book based on this sql coding for this perticular db

The size is 60 mgbytes it would have to be done by email.

 

[MSAccessRookie]

I have a hymnal DB I need some help.

I am trying to set up a list box two of them with name of song for one and hymnal for the other.

This is the sql coding
SELECT songsoncdandhymnalapr12007.ID, songsoncdandhymnalapr12007.NameofSong, songsoncdandhymnalapr12007.hymnalyes, songsoncdandhymnalapr12007.[hymnal/cdname], songsoncdandhymnalapr12007.MEdiatype, songsoncdandhymnalapr12007.hymnalyes, songsoncdandhymnalapr12007.cdno, songsoncdandhymnalapr12007.composernum, songsoncdandhymnalapr12007.[Composer name], songsoncdandhymnalapr12007.ArrangerNAme, songsoncdandhymnalapr12007.ArrangerNumber, songsoncdandhymnalapr12007.authornumber, songsoncdandhymnalapr12007.AuthorandKey, songsoncdandhymnalapr12007.Scripture, songsoncdandhymnalapr12007.key, songsoncdandhymnalapr12007.Tune
FROM songsoncdandhymnalapr12007;

however the coding it don' like and all I need is a query to go from name of song select to name of Hymnal.
Can anyone help me out on this mess?

I can read books and books about it to a certain extent it explains what a union query is People don't realize it's no use if their is no book based on this sql coding for this perticular db

The size is 60 mgbytes it would have to be done by email.

What doesn't it like? I see three things that I would do differently, none of which should create a problem for you.

1. Some of your names have spaces or special characters.
   1. You have surrounded these names with [], making them acceptable to MS Access at this time.
   2. I would change the names to remove the non standard characters for ongoing future comptibility.
      1. [Composer name] becomes ComposerName
      2. [hymnal/cdname] becomes HymnalOrCDName
2. Some of your names, including the table name, are very long and the potential for spelling errors exists.
   1. This is not a always problem, it is a personal preference for me
   2. I would make the names shorter, but at the same time retain the way they define their purpose
      1. songsoncdandhymnalapr12007 becomes SCHA2007
3. You refer to the tablename in each column selection. In this instance, since there is only one table, that is not required. If you still want it, an Alias makes it easier to read.
   • You could say

[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]SELECT ID, NameofSong, hymnalyes, [hymnal/cdname], [/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]MEdiatype, hymnalyes, cdno, composernum, [Composer name], ArrangerNAme, [/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]ArrangerNumber, authornumber, AuthorandKey, Scripture, key, Tune[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]FROM songsoncdandhymnalapr12007;[/FONT][/COLOR]
[/FONT][/COLOR]

• • Or if you still want/need a Table Identifier, you could use an Alias

[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]SELECT SCHA2007.ID, SCHA2007.NameofSong, SCHA2007.hymnalyes, [/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]SCHA2007.[hymnal/cdname], SCHA2007.MEdiatype, SCHA2007.hymnalyes, [/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]SCHA2007.cdno, SCHA2007.composernum, SCHA2007.[Composer name], [/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]SCHA2007.ArrangerNAme, SCHA2007.ArrangerNumber, SCHA2007.authornumber, [/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]SCHA2007.AuthorandKey, SCHA2007.Scripture, SCHA2007.key, SCHA2007.Tune[/FONT][/COLOR][/FONT][/COLOR][/FONT][/COLOR]
[COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana][COLOR=black][FONT=Verdana]FROM songsoncdandhymnalapr12007 As S[/FONT][/COLOR][FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]CHA2007;[/FONT][/COLOR][/COLOR][/FONT]
[/FONT][/COLOR]
[/FONT][/COLOR]

​

Note that if your query is part of VB Code, then #2 (the length issue) could be a factor.
​