Access Database Questions

[burningsoul]

I've been doing web design for 10 years now and just now teaching myself some ColdFusion. Installation and everything is fine, can run queries, etc...

For reference, my site that I wish to change over to some CF stuff is at

http://www.burningsouls.com.

I am working on the song interpretations under the lyrics > song interpretations drop down in the menu.

I need some advice on setting up my tables in Access. I currently have two tables, one named Tracks and the other named Interps.

In the Tracks table I have ID, AlbumName, SongName, Updated and Total as my columns. I filled the information in accordingly.

In the Interps table I have ID, AlbumName, SongName, UserName, UserEmail, SongInt and Updated for my columns.

I do not have any relationships defined but I have been able to run some SQL queries on the information and display everything as you see it currently on my site.

My question is, how can I improve on this design? I'm sure there is an easier way to set up these tables and relate them to one other. I am also having trouble running a query in Access to count the number of song interpretations (SongInt in the Interps table) and display that under the Total column in the Tracks table.

Any help or advice would be greatly appreciated!

 

[ZmrAbdulla]

For a sample download from Microsoft


________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[burningsoul]

thanks for that link. i found a better way to separate the albums and the tracklist...working on a couple tables for those now...

would still like to modify the interpretations table a bit...that download didn't have much info on something like that...

 

[nbateman]

Burningsoul,

How about the following which has the following advantages:
- Names make bad keys so aren't used (they change too easily, which really screws any relationships based on them)
- Compound keys are ok, but best avoided (in my opinion) in favour of a single, numeric, and meaningless primary key ...
- ... which makes joining the tables really easy and efficient (see example below)
- Duplication (of Album and Song names) is reduced. This is not just a space issue; more importantly, if you need to change a name (perhaps you spelt it incorrectly), you shouldn't have to change it multiple times. Also, if you select all the interpretations for a given song name you will miss any that are spelt incorrectly (including additional spaces, puntuation, or upper/lower case issues)


Album
=====
AlbumID Primary key
AlbumName

Song
====
SongId Primary key
AlbumId Foreign key
SongName
Updated

Interpretation
==============
InterpId Primary key
SongId Foreign key
UserEmail
SongInterpretation
Updated

Personally, I wouldn't hold the total column, as it will need updating everytime an interpretation is added or removed. To count the interpretations:

SELECT SongName, Count(InterpId)
FROM Song INNER JOIN Interpretation ON Song.SongId = Interpretation.SongId
GROUP BY SongName;


Finally, relationships exist in data regardless of whether you tell Access about them or not. You don't need to define them to in order to join and query tables. You do need them if you want Access to enforce the referential integrity for you (e.g. an interpretation cannot exist for a song that doesn't).

Nick.

 

[burningsoul]

Nick,

Thank you very much for the help. Since posting this and checking the link Zmr posted, I was able to create the tables exactly as you showed above!

However, you brought up the point of counting the records. The problem I'm facing is this:

When running the query to display all the songs within an album, I need to count the interpretations associated with EACH SONG from the query and display them all on the same page.

Typing this I am getting an idea of how to do this. Is it possible to nest a query within a query? For instance, I currently have: (I've removed html formatting for easier reading)

<cfoutput query="SongName">
<a href="/interpretations/IntDisplay.cfm?TrackID=#TrackID#">#TrackTitle#</a>
#IntCount
#Updated#
</cfoutput>

Am I able to just put another <cfoutput> tag within this one, like this:

<cfoutput query="SongName">
<a href="/interpretations/IntDisplay.cfm?TrackID=#TrackID#">#TrackTitle#</a>
<cfoutput>#SongName.RecordCount#</cfoutput>
#Updated#
</cfoutput>

Guess there's only one way to find out. If not, how can I accomplish this?

 

[nbateman]

Now that's a cold fusion question that I can't help you with.

While I said "I wouldn't hold the total column", that is with all things being equal. If cold fusion gives you a reason to hold and update Total it in a table column, just do it.
The worse that can happen is it doesn't automatically get updated sometimes, but that is easily recitfied by a periodic (weekly? daily? hourly even?) query that resets all the Totals to their correct value.

Nick.

 

[burningsoul]

Well I messed around with all kinds of ways to count the record within each track, but couldn't get it to work so I'm back at having a Count Field.

You mentioned running a periodic query to reset all of the totals. I think I tried doing something like this but couldn't figure it out. And how would I make it reset the total to the correct value?

Jay

 

[nbateman]

Jay,

Use two queries:

First a Make table query (a table called InterpCount in this example):
SELECT SongId, Count(InterpId) AS ITotal INTO InterpCount FROM Interpretation GROUP BY SongId;

Next an Update query:
UPDATE Song INNER JOIN InterpCount ON Song.SongId = InterpCount.SongId SET Song.Total = [ITotal];

Run both from a macro using the following actions:
- Set Warnings (No)
- OpenQuery (the Make query)
- OpenQuery (the update query)
- Quit

Initiate the macro from "Scheduled Tasks" in the control panel with
"...\MSACCESS.EXE" "...\db1.mdb" /x NameOfMacro (replace ... with the full paths)

Nick.

 

[r937]

you don't need to have a column to store something that can easily be obtained by a join query with COUNT and GROUP BY

the issue you have with showing counts per record within the CFOUTPUT can be resolved with the correct query

r937.com | rudy.ca

 

[burningsoul]

Nick,

I'm not quite familiar with creating macros in Access. Also, I assume I'm making these queries in Access, however, do I need to put the SQL code in the Expression Builder? If so, it says I have an incorrect syntax and I can't spot it.

When this macro is working, it should be able to count each Interpretation for each song and update each track with the correct number, which is in a different table (I have called TrackList, correct? At least, that is what I want to be able to do! I need the TrackList table updated, I have the following fields (TrackID, TrackNumber, TrackTitle, AlbumID, IntCount, Updated). The IntCount field is what I need to update for each song. Hope that clears it up a little bit.

 

[burningsoul]

Update:

I was able to create a macro and set the action to RunSQL and I entered the updated SQL in and it created the table and counted the Interpretations just fine. However, you said to create a query first, then have the macro run the queries...is this step necessary or can I just have the macro run the SQL commands for the Make and Update queries...

 

[burningsoul]

Can you not edit your own posts???

I have the macro running through Access just fine, it counts each Interp for each Track, then the second command updates the TrackList table with the IntCount for each song!

Thanks so much for that JOIN command, I need to look into these more.

Now, my next task is to do something similiar, only I want to be able to run a macro to check the date each song was last updated with an interpretation. in my Interpretations table I have a column named 'Added' which puts in the date I add it. Then in TrackList table I have a column named 'Updated' which as of right now, manually displayed the last time the song was updated.

Sounds like I will be using a similar JOIN command, only I need to find the newest date (would that require a sort of the dates first?) and then update the TrackList table with the correct date...

 

[r937]

you don't need to carry a redundant column for the last date updated, either

r937.com | rudy.ca

 

[nbateman]

RunSQL and OpenQuery action achieve the same thing for you; use whichever you prefer.

For the newest date, use max(Updated) instead of Count(InterpId). In fact, you can do them at the same time as in:

SELECT SongId, Count(InterpId) AS ITotal, Max(Updated) AS Newest INTO InterpCount FROM Interpretation GROUP BY SongId;

 

[burningsoul]

Thanks so much Nick, you've really helped a lot on this project! I hope to go live with it as soon as I switch to a server that supports CF.

I put the second Update query in a separate command. Is it possible to combine the IntCount and Newest Update queries into one query? I'm sure you can update multiple fields in one table. Could I just add an AND after setting the IntCount field and do SET TrackList.Updated = [Newest];

 

[nbateman]

Jay,

UPDATE Song INNER JOIN InterpCount ON Song.SongId = InterpCount.SongId SET Song.Total = [ITotal], SET Song.Updated = [Newest];

Nick.