Programming with M.Access

[LeazesMag 0]

Is anyone a bit of an expert on this ???????

 

I want a bit of advice if poss, I am using Access2000 with windows xp

 

Edit: Sorry, wrong section please move ........

Edited September 6, 2006 by LeazesMag

[Holden McGroin 6647]

Whats your question?

[LeazesMag 0]

Whats your question?

194164[/snapback]

 

Basically, I can't create a subdatasheet - where it shows the + sign to click on and view a subdatasheet.

 

I think the problem MAY be that the version of Access I am using is too old - meaning it is a problem with primary keys and the table relationship.

 

All I want to do is make up a database of my CD's/Albums, and subdata the tracks by clicking on the +

 

But there is something in the format that it doesn't like.

[Tom 14013]

I blame the Board.

 

 

There was no access pre - 1992

[LeazesMag 0]

anyone but Bill Gates eh

[gram 0]

anyone but Bill Gates eh 

194168[/snapback]

Not sure what youre trying to do but a flat file table would be enough for this type of database - linked to a basic query. You could do that and link it to a report.

You trying to build a system for them?

If that is the case then I would do a web based one using something like mysql or just a dreamweaver thing linked to your access flat file.

 

Alternatively, I have thousands of cds and just put them in alphabetical order.

[Lazarus 0]

or you could just download some free software where you type in the bar code number and it gets all the info for you.....

[LeazesMag 0]

tks for the replies lads. Access is fairly new to me but all I would like to do is click on an album title ie the +, and display the tracks. I can't get the + to appear despite trying to create a one-to-many relationship etc etc. The first database has the album, band, year of release, etc etc, I can't see how this can be done with a flat database. If I'm going about it the wrong way in a format thats not really going to work fair enough.

 

If I need a newer version of Access then I can maybe get one.

[peasepud 59]

tks for the replies lads. Access is fairly new to me but all I would like to do is click on an album title ie the +, and display the tracks. I can't get the + to appear despite trying to create a one-to-many relationship etc etc. The first database has the album, band, year of release, etc etc, I can't see how this can be done with a flat database. If I'm going about it the wrong way in a format thats not really going to work fair enough.

 

If I need a newer version of Access then I can maybe get one.

194276[/snapback]

 

It sounds like what you're doing is correct, sounds like two tables...

 

Table 1 - CD

ID

CD Title

Artist

Year

etc

 

Table 2 - Tracks

ID

CD_ID

Track Name

Running Time

etc

 

 

In both cases make the ID fields the Unique Primary Key.

 

Link the two together via a One to Many on the ID and CD_ID field and it should work, if you look at the SQL view of the Query it should look summit like this:

 

SELECT * FROM Tracks INNER JOIN CD WHERE cd.ID = Tracks.CD_ID;

 

 

What exactly is happening? nothing at all? or are you getting an error message?

[LeazesMag 0]

tks for the replies lads. Access is fairly new to me but all I would like to do is click on an album title ie the +, and display the tracks. I can't get the + to appear despite trying to create a one-to-many relationship etc etc. The first database has the album, band, year of release, etc etc, I can't see how this can be done with a flat database. If I'm going about it the wrong way in a format thats not really going to work fair enough.

 

If I need a newer version of Access then I can maybe get one.

194276[/snapback]

 

It sounds like what you're doing is correct, sounds like two tables...

 

Table 1 - CD

ID

CD Title

Artist

Year

etc

 

Table 2 - Tracks

ID

CD_ID

Track Name

Running Time

etc

 

In both cases make the ID fields the Unique Primary Key.

 

Link the two together via a One to Many on the ID and CD_ID field and it should work, if you look at the SQL view of the Query it should look summit like this:

 

SELECT * FROM Tracks INNER JOIN CD WHERE cd.ID = Tracks.CD_ID;

 

What exactly is happening? nothing at all? or are you getting an error message?

194303[/snapback]

 

I have realised I am running '97, and have upgraded it to office 2000 to see if it works better [ i thought i put 2000 on when I got the PC but obviously not] ...I have been told it will do so I will try again later mate and let you know. What was happening was simply that I was not getting the + sign , and that is the format I wanted to do it in as it is the way I am learning to do it. I realise I can do it via reports/queries but I'm not quite at that stage yet .... the annoying thing is all the reports/queries section of access quite easy, it is the relationship and subdata sheets that is a bit difficult to grasp or should I say I did get it right and thought it was wrong because the old version wasn't doing what the up to date ones do.

 

The only messages I am getting is validation ones, sometimes. I have been told to use a double primary key to get a "one to many" relationship but it won't let me do this either.

 

Your table 1 above is exactly what I have for table 1, and in table 2 I have got Album title [again - to connect a relationship] along with Track number, track title and a section for comments etc.

[gram 0]

tks for the replies lads. Access is fairly new to me but all I would like to do is click on an album title ie the +, and display the tracks. I can't get the + to appear despite trying to create a one-to-many relationship etc etc. The first database has the album, band, year of release, etc etc, I can't see how this can be done with a flat database. If I'm going about it the wrong way in a format thats not really going to work fair enough.

 

If I need a newer version of Access then I can maybe get one.

194276[/snapback]

 

It sounds like what you're doing is correct, sounds like two tables...

 

Table 1 - CD

ID

CD Title

Artist

Year

etc

 

Table 2 - Tracks

ID

CD_ID

Track Name

Running Time

etc

 

In both cases make the ID fields the Unique Primary Key.

 

Link the two together via a One to Many on the ID and CD_ID field and it should work, if you look at the SQL view of the Query it should look summit like this:

 

SELECT * FROM Tracks INNER JOIN CD WHERE cd.ID = Tracks.CD_ID;

 

What exactly is happening? nothing at all? or are you getting an error message?

194303[/snapback]

 

I have realised I am running '97, and have upgraded it to office 2000 to see if it works better [ i thought i put 2000 on when I got the PC but obviously not] ...I have been told it will do so I will try again later mate and let you know. What was happening was simply that I was not getting the + sign , and that is the format I wanted to do it in as it is the way I am learning to do it. I realise I can do it via reports/queries but I'm not quite at that stage yet .... the annoying thing is all the reports/queries section of access quite easy, it is the relationship and subdata sheets that is a bit difficult to grasp or should I say I did get it right and thought it was wrong because the old version wasn't doing what the up to date ones do.

 

The only messages I am getting is validation ones, sometimes. I have been told to use a double primary key to get a "one to many" relationship but it won't let me do this either.

 

Your table 1 above is exactly what I have for table 1, and in table 2 I have got Album title [again - to connect a relationship] along with Track number, track title and a section for comments etc.

194802[/snapback]

Didnt realise you were going to put the songs on it leazes. To be honest I would probably just decice to use something like itunes and burn your cds onto it. That way gracenote creates what you want - i.e. gives you names of albums and song lists as well as keeping a copy of cd on pc. doesnt take up much memory relative to an average pcs storage capacity. Also means you dont have to type anything in.

If you do persevere with the database then feel free to email the idea to me and i will create it for you. wont take long. depends on what you want doing.

If its just a basic one ot many relationship then create the table as pud says then go to relationship and link from table one ot two. But be very careful with the option for referential integrity. This is particularly important when you dont use the autofields and depends on you to enter accurate data.

Cheers

[LeazesMag 0]

tks for the replies lads. Access is fairly new to me but all I would like to do is click on an album title ie the +, and display the tracks. I can't get the + to appear despite trying to create a one-to-many relationship etc etc. The first database has the album, band, year of release, etc etc, I can't see how this can be done with a flat database. If I'm going about it the wrong way in a format thats not really going to work fair enough.

 

If I need a newer version of Access then I can maybe get one.

194276[/snapback]

 

It sounds like what you're doing is correct, sounds like two tables...

 

Table 1 - CD

ID

CD Title

Artist

Year

etc

 

Table 2 - Tracks

ID

CD_ID

Track Name

Running Time

etc

 

In both cases make the ID fields the Unique Primary Key.

 

Link the two together via a One to Many on the ID and CD_ID field and it should work, if you look at the SQL view of the Query it should look summit like this:

 

SELECT * FROM Tracks INNER JOIN CD WHERE cd.ID = Tracks.CD_ID;

 

What exactly is happening? nothing at all? or are you getting an error message?

194303[/snapback]

 

 

I have realised I am running '97, and have upgraded it to office 2000 to see if it works better [ i thought i put 2000 on when I got the PC but obviously not] ...I have been told it will do so I will try again later mate and let you know. What was happening was simply that I was not getting the + sign , and that is the format I wanted to do it in as it is the way I am learning to do it. I realise I can do it via reports/queries but I'm not quite at that stage yet .... the annoying thing is all the reports/queries section of access quite easy, it is the relationship and subdata sheets that is a bit difficult to grasp or should I say I did get it right and thought it was wrong because the old version wasn't doing what the up to date ones do.

 

The only messages I am getting is validation ones, sometimes. I have been told to use a double primary key to get a "one to many" relationship but it won't let me do this either.

 

Your table 1 above is exactly what I have for table 1, and in table 2 I have got Album title [again - to connect a relationship] along with Track number, track title and a section for comments etc.

194802[/snapback]

Didnt realise you were going to put the songs on it leazes. To be honest I would probably just decice to use something like itunes and burn your cds onto it. That way gracenote creates what you want - i.e. gives you names of albums and song lists as well as keeping a copy of cd on pc. doesnt take up much memory relative to an average pcs storage capacity. Also means you dont have to type anything in.

If you do persevere with the database then feel free to email the idea to me and i will create it for you. wont take long. depends on what you want doing.

If its just a basic one ot many relationship then create the table as pud says then go to relationship and link from table one ot two. But be very careful with the option for referential integrity. This is particularly important when you dont use the autofields and depends on you to enter accurate data.

Cheers

194847[/snapback]

 

thanks for the offer mate. I will have another go - not today - and take you up on it if I come across any probs.

[LeazesMag 0]

tks for the replies lads. Access is fairly new to me but all I would like to do is click on an album title ie the +, and display the tracks. I can't get the + to appear despite trying to create a one-to-many relationship etc etc. The first database has the album, band, year of release, etc etc, I can't see how this can be done with a flat database. If I'm going about it the wrong way in a format thats not really going to work fair enough.

 

If I need a newer version of Access then I can maybe get one.

194276[/snapback]

 

It sounds like what you're doing is correct, sounds like two tables...

 

Table 1 - CD

ID

CD Title

Artist

Year

etc

 

Table 2 - Tracks

ID

CD_ID

Track Name

Running Time

etc

 

 

In both cases make the ID fields the Unique Primary Key.

 

Link the two together via a One to Many on the ID and CD_ID field and it should work, if you look at the SQL view of the Query it should look summit like this:

 

SELECT * FROM Tracks INNER JOIN CD WHERE cd.ID = Tracks.CD_ID;

 

 

What exactly is happening? nothing at all? or are you getting an error message?

194303[/snapback]

 

I have named the "CD_ID" field "Track Number" and have also inserted a field called "Album Title". Which of these should I link the ID from table 1 to .... and how do I link it to 2 fields in the 2nd table, it won't let me do it ???

 

 

Table 1 has ID, Title, Artist, Year, Format, Comments

 

Table 2 has ID, Album Title, Track Nr, Song title, comments

Edited September 7, 2006 by LeazesMag

[gram 0]

tks for the replies lads. Access is fairly new to me but all I would like to do is click on an album title ie the +, and display the tracks. I can't get the + to appear despite trying to create a one-to-many relationship etc etc. The first database has the album, band, year of release, etc etc, I can't see how this can be done with a flat database. If I'm going about it the wrong way in a format thats not really going to work fair enough.

 

If I need a newer version of Access then I can maybe get one.

194276[/snapback]

 

It sounds like what you're doing is correct, sounds like two tables...

 

Table 1 - CD

ID

CD Title

Artist

Year

etc

 

Table 2 - Tracks

ID

CD_ID

Track Name

Running Time

etc

 

 

In both cases make the ID fields the Unique Primary Key.

 

Link the two together via a One to Many on the ID and CD_ID field and it should work, if you look at the SQL view of the Query it should look summit like this:

 

SELECT * FROM Tracks INNER JOIN CD WHERE cd.ID = Tracks.CD_ID;

 

 

What exactly is happening? nothing at all? or are you getting an error message?

194303[/snapback]

 

I have named the "CD_ID" field "Track Number" and have also inserted a field called "Album Title". Which of these should I link the ID from table 1 to .... and how do I link it to 2 fields in the 2nd table, it won't let me do it ???

 

 

Table 1 has ID, Title, Artist, Year, Format, Comments

 

Table 2 has ID, Album Title, Track Nr, Song title, comments

194898[/snapback]

basically you need to do the following:

Table one

Id as primary key and title, artist, album

Table 2

Id (no primary key in this table), tracks (but if e.g. The Clash is 1 in table 1, then you need to label every track on this album 1 in the id field in this table)

I would put track number, year, etc in this table

 

Relate the two id fields from table 1 to table 2. that works.

 

 

There is an easier way but its late and brain isnt working.

 

Try it though. It will work.

[peasepud 59]

I have named the "CD_ID" field "Track Number" and have also inserted a field called "Album Title". Which of these should I link the ID from table 1 to .... and how do I link it to 2 fields in the 2nd table, it won't let me do it  ???

 

 

Table 1 has ID, Title, Artist, Year, Format, Comments

 

Table 2 has ID, Album Title, Track Nr, Song title, comments

194898[/snapback]

 

You should never use the Album Title to link the tables as

a) its easy to misspell an occurence,

it is possible to have more than one album with the same name ie Greatest Hits

and c) it means typing in the name a dozen times and just imagine if you have a copy of Fiona Apples hit album "When the Pawn Hits the Conflicts He Thinks Like a King What He Knows Throws the Blows When He Goes to the Fight and He'll Win the Whole Thing 'Fore He Enters the Ring There's No Body to Batter When Your Mind Is Your Might So When You Go Solo, You Hold Your Own Hand and Remember That Depth Is the Greatest of Heights and If You Know Where You Stand, Then You Know Where to Land and If You Fall It Won't Matter, because You'll Know That You're Right"

 

Create a new field in the tracks table called Album_ID and in there put the unique ID of the album table so if "Sgt Peppers" is the 1st album then all the tracks for that will have 001 or whatever in them

 

Also make sure that the format of the Album_ID field is the same format as the Unique ID of the Album table.

[adios 717]

You should never use the Album Title to link the tables as

a) its easy to misspell an occurence,

it is possible to have more than one album with the same name ie Greatest Hits

and c) it means typing in the name a dozen times and just imagine if you have a copy of Fiona Apples hit album "When the Pawn Hits the Conflicts He Thinks Like a King What He Knows Throws the Blows When He Goes to the Fight and He'll Win the Whole Thing 'Fore He Enters the Ring There's No Body to Batter When Your Mind Is Your Might So When You Go Solo, You Hold Your Own Hand and Remember That Depth Is the Greatest of Heights and If You Know Where You Stand, Then You Know Where to Land and If You Fall It Won't Matter, because You'll Know That You're Right"

 

Create a new field in the tracks table called Album_ID and in there put the unique ID of the album table so if "Sgt Peppers" is the 1st album then all the tracks for that will have 001 or whatever in them

 

Also make sure that the format of the Album_ID field is the same format as the Unique ID of the Album table.

194975[/snapback]

 

You should introduce Leazes to the concept of copy & paste tbh.

[Gemmill 45253]

How on earth do you find the time to do something like this whilst simultaneously defending the honour of Freddy Shepherd? Oh and WHY?!

[MrBass 2661]

How on earth do you find the time to do something like this whilst simultaneously defending the honour of Freddy Shepherd?  Oh and WHY?!

195332[/snapback]

 

Doesn't hurt to have an inventory of all your CD's tbf

[Gemmill 45253]

I bet you colour co-ordinate your sock drawers too. Dweebs!

[LeazesMag 0]

Spot on, it works in the way I want. Many thanks PP and gram...smashing.

 

Now I will spend a few months inputting my entire CD, Album and tape collection ....

 

I upgraded to Access 2000 as well BTW. So overall, a decent job well done and worth it.

 

No primary key was an interesting idea, I'll remember that for future use.

[LeazesMag 0]

I bet you colour co-ordinate your sock drawers too.  Dweebs!

195385[/snapback]

 

what colour are yours, pink

[Craig 6682]

How on earth do you find the time to do something like this whilst simultaneously defending the honour of Freddy Shepherd?  Oh and WHY?!

195332[/snapback]

 

Doesn't hurt to have an inventory of all your CD's tbf

195350[/snapback]

 

True.....

 

But instead of writing one, why not use the CD Library Database template which gets shipped with MS Office?

[LeazesMag 0]

How on earth do you find the time to do something like this whilst simultaneously defending the honour of Freddy Shepherd?  Oh and WHY?!

195332[/snapback]

 

Doesn't hurt to have an inventory of all your CD's tbf

195350[/snapback]

 

True.....

 

But instead of writing one, why not use the CD Library Database template which gets shipped with MS Office?

195553[/snapback]

 

I'd not heard about it until mentione on here Craig. I wanted to do it myself though and do and learn a little of Access. Plus - I have lots of digitized albums, with bonus tracks, self made compilations, and tbh some of my albums and individual tracks - including singles - might not be available, I've got a lot of pop and rock music from the 60, 70's and 80's. Plus bootlegs. I also want to make any relevant comments - which is why I have put the column it , with anything at all ie "saw band at the Mayfair"...any old crap.

 

Wouldn't mind any info on this Library database though, especially if you can actually download and/or get info on rare old singles etc

[Craig 6682]

To be fair LM, I was being facetious and I don't think they actually ship it any more now that CDDB is in operation on the web.

 

A CD library is a good starting point for learning MS Access though.

[LeazesMag 0]

To be fair LM, I was being facetious and I don't think they actually ship it any more now that CDDB is in operation on the web.

 

A CD library is a good starting point for learning MS Access though.

 

funny bastard eh

 

(BTW - I don't like this "guided mode".....what is the point of it .... )