Access Querying Question

[stinkynathan]

I'm working on DB software for a music program to help manage their music. I had a DB guy help me with the structural things and everything on the form entry side is gravy. I'm having a problem with reporting and querying in two cases, though.

Here are the affected tables and fields:
Music (for all of the pieces of music)
PK_ID
FK_Composer
FK_Arranger
FK_Style1
FK_Style2

Composers_Arrangers (composers and arrangers, obviously)
PK_ID
First_Name
Last_Name

Styles (for the different styles of music)
PK_ID
Style_Description

Both music.FK_Composer and music.FK_Arranger are related to Composers_Arrangers.PK_ID. Music.FK_Style1 and Music.FK_Style2 are related to Styles.PK_ID. This is necessary because a person could be a composer or arranger (or both) on a piece of music.

The problem comes when trying to generate a report or query to list composer, arranger, style1, and style2 of a piece of music. I can get either the composer or arranger but not both, and I can get style1 or style2 but not both.

I have not yet been able to find a solution to this problem. It's redundant to have separate tables for composers and arrangers and multiple style tables, but that seems to be the obvious, cop-out solution. Any suggestions to make this work?

 

[WannaFly]

I'm not that familiar with Access, but you can try using table aliases:

Select Music.*, CA1.*, CA2.* from Music, Composers_Arrangers CA1, Composers_Arrangers CA2
where CA1.PK_ID=Music.FK_Composer
AND CA2.PK_ID=Music.FK_Arranger

 

[KLin]

Originally posted by: WannaFly
I'm not that familiar with Access, but you can try using table aliases:

Select Music.*, CA1.*, CA2.* from Music, Composers_Arrangers CA1, Composers_Arrangers CA2
where CA1.PK_ID=Music.FK_Composer
AND CA2.PK_ID=Music.FK_Arranger

The only change I would make is I would use some field aliases to determine who is the arranger and who is the composer.

i.e CA1.Name as Composer, CA2.Name as Arranger.

But that syntax should work in an access query.

 

[stinkynathan]

Thank you to WannaFly and KLin. This is doing nearly exactly what I want. The only problem I'm having is that it is only returning nonempty results. Is there any way to make that query also return empty results? Some pieces of music may not always have a composer or arranger. A lot of this music is REALLY old and physically damaged; sometimes it's damaged in such a way that we can't read information on it.

Originally posted by: KLin

Originally posted by: WannaFly
I'm not that familiar with Access, but you can try using table aliases:

Select Music.*, CA1.*, CA2.* from Music, Composers_Arrangers CA1, Composers_Arrangers CA2
where CA1.PK_ID=Music.FK_Composer
AND CA2.PK_ID=Music.FK_Arranger

The only change I would make is I would use some field aliases to determine who is the arranger and who is the composer.

i.e CA1.Name as Composer, CA2.Name as Arranger.

But that syntax should work in an access query.

Yes, I was already intending to use the field aliases. The users will enter the name as First, Middle, Last, and Suffix in separate fields. I'm using a field alias to combine all four of those fields into "Composer_Fullname."

Essentially like this:
First & " " & Middle & " " & Last & " " & Suffix as Composer_Full_Name

While we're at it... Is this the best way to do this? Is there any way to do this alias conditionally? Right now, if a composer doesn't have any of those parts, I get an extra space between the information I DO have. It's only a minor issue right now as it's only affecting the output of my reports, but it bothers me a little that I can't think of a way to not add spaces when I don't need to.

 

[KLin]

You'll want to change the query to left joins then

SELECT columns
FROM Music M Left Join Composers_Arrangers CA1 on m.pk_id = CA1.fk_composer LEFT JOIN Composers_Arrangers CA2 on M.pk_id = CA2.fk_arranger

 

[stinkynathan]

Originally posted by: KLin
You'll want to change the query to left joins then

SELECT columns
FROM Music M Left Join Composers_Arrangers CA1 on m.pk_id = CA1.fk_composer LEFT JOIN Composers_Arrangers CA2 on M.pk_id = CA2.fk_arranger

Brilliant. Thanks for the help.

Any way to do the field aliases conditionally? It would be nice be able to check if a field is empty before adding it to the alias so I don't get an extra space between the first and last name if we don't know the middle name.

It looks like this now:
[CA1.First_Name] & " " & [CA1.Middle_Name] & " " & [CA1.Last_Name] & " " & [CA1.Suffix] AS Composer_Full_Name

Would be nice if I could do:
(IF NOT NULL ([CA1.First_Name] & " " &) (IF NOT NULL ([CA1.Middle_Name] & " " &) (IF NOT NULL ([CA1.Last_Name] & " " &) (IF NOT NULL ([CA1.Suffix]) AS Composer_Full_Name

If you can catch my drift through that.

 

[GeekDrew]

I don't recall whether or not Access recognizes "trim" by default, but here's what I use in several other apps:

Trim(Trim(Trim(first_name) & " " & Trim(middle_name)) & " " & Trim(last_name)) AS full_name

That's probably overkill for you, but I use it because my data may have leading or trailing white space inside the column data. Assuming you don't have that issue, and if Access knows about rtrim rather than trim, then maybe the following:

RTrim(first_name & " " & middle_name) & " " & last_name AS full_name

 

[KLin]

Originally posted by: stinkynathan

Originally posted by: KLin
You'll want to change the query to left joins then

SELECT columns
FROM Music M Left Join Composers_Arrangers CA1 on m.pk_id = CA1.fk_composer LEFT JOIN Composers_Arrangers CA2 on M.pk_id = CA2.fk_arranger

Brilliant. Thanks for the help.

Any way to do the field aliases conditionally? It would be nice be able to check if a field is empty before adding it to the alias so I don't get an extra space between the first and last name if we don't know the middle name.

It looks like this now:
[CA1.First_Name] & " " & [CA1.Middle_Name] & " " & [CA1.Last_Name] & " " & [CA1.Suffix] AS Composer_Full_Name

Would be nice if I could do:
(IF NOT NULL ([CA1.First_Name] & " " &) (IF NOT NULL ([CA1.Middle_Name] & " " &) (IF NOT NULL ([CA1.Last_Name] & " " &) (IF NOT NULL ([CA1.Suffix]) AS Composer_Full_Name

If you can catch my drift through that.

http://msdn.microsoft.com/en-u...ydhh0d%28VS.71%29.aspx

 

[stinkynathan]

Originally posted by: GeekDrew
I don't recall whether or not Access recognizes "trim" by default, but here's what I use in several other apps:

Trim(Trim(Trim(first_name) & " " & Trim(middle_name)) & " " & Trim(last_name)) AS full_name
...

This worked perfectly. Thank you.

Now the problem is trying to report data back to the user based on an option they've chosen from a combo box. I think I've figured it out based on the Help Files....