Normalisation help please

Helium_Junkie said:
What?
The typo was "Atrist", not "Artiste" ... what are you talking about?
Click to expand...


The typo had been pointed out ........ the glaring mistake of using "artist" instead of "artiste", I thought was important ........ but yeah, I pretty much hate all students ;) unless they can show me how to use my newfangled N95 ....... ;) :D
 
i want it to contain data on loads of vinyl and cd, i know by looking at the cd and vinyl table you cant tell what songs are on what cd or vinyl, but im pretty sure you'd use a query to do that

helium - i get ya now :D thanks
 
Smiley Man said:
i want it to contain data on loads of vinyl and cd, i know by looking at the cd and vinyl table you cant tell what songs are on what cd or vinyl, but im pretty sure you'd use a query to do that
Click to expand...


You're missing the point, you cant ever have more than two fields in a table where the primary key has only two possible values. Yes or No.

Primary keys MUST be unique. You cant have two fields in there with the key as "Yes".

You need to change the Vinyl and CD tables to have ID NUMBERS as the PRIMARY KEY, and then once that is done go to your remix table and change the "CD?" to be the same type as your new primary key, and link them the same as you have now.

Then you will indeed be able to use a query to find all remix's on a certain CD.
 
Last edited:
Helium_Junkie said:
You're missing the point, you cant ever have more than two fields in a table where the primary key has only two possible values. Yes or No.

Primary keys MUST be unique. You cant have two fields in there with the key as "Yes".
Click to expand...

yeah that makes sense, the only way i can see of doing it now is to merge the cd and vinyl table into one though

i'm getting there, its just god damn keys i cant get my head around
 
Smiley Man said:
yeah that makes sense, the only way i can see of doing it now is to merge the cd and vinyl table into one though

i'm getting there, its just god damn keys i cant get my head around
Click to expand...

Vinyl Table:

Primary Key: Autonumber : Vinyl_ID
Name: Text
Date Released: Date

CD Table:

Primary Key: Autonumber : CD_ID
Name: Text
Date Released: Date

Remix Table:

Foreign keys:
CD_ID
Vinyl_ID

You do not need to merge anything, just change the data type of the CD? field to a number.
 
Helium_Junkie said:
Vinyl Table:

Primary Key: Autonumber : Vinyl_ID
Name: Text
Date Released: Date

CD Table:

Primary Key: Autonumber : CD_ID
Name: Text
Date Released: Date

Remix Table:

Foreign keys:
CD_ID
Vinyl_ID

You do not need to merge anything, just change the data type of the CD? field to a number.
Click to expand...

right ive just set it up like that and it still doesnt work :( when i try and save the remix table it tells me i need matching values in the CD and vinyl table (whichever one i put a value in), is this just access being a heap of crap? ive got referential integrity on in the relationship settings
 
Question: Are you saving a remix that has data in the foreign key values for CD or Vinyl?

If so, do you have a field in the CD or Vinyl table with the foreign key you've put in? You MUST create the cd/vinyl before you use its ID elsewhere.

If not, check the properties of the CD/Vinyl foreign keys to make sure they arent set as "not null".
 
Have you actually got data in the CD and vinyl tables? If not you can't start with the remix table and expect it to work. You have to fill in your primary data before you link it to your secondary tables.
 
DaleyB said:
One solution - uninstall Access and never use it for normalisation again.

May not be appropriate, but seriously. Use SQL
Click to expand...

sadly i cant use SQL - i havent got long left to do the assignment and i only know a tiny bit of SQL

dolph - no theres no data in the cd and vinyl tables, thats what i thought was wrong, im just trying to figure out a way round it
 
Smiley Man said:
sadly i cant use SQL - i havent got long left to do the assignment and i only know a tiny bit of SQL

dolph - no theres no data in the cd and vinyl tables, thats what i thought was wrong, im just trying to figure out a way round it
Click to expand...


.... put data in there? :p

If you are trying to save a remix entry which doesn't have any values for vinyl or cd then that's different - is this the case?
 
Last edited:
Smiley Man said:
sadly i cant use SQL - i havent got long left to do the assignment and i only know a tiny bit of SQL

dolph - no theres no data in the cd and vinyl tables, thats what i thought was wrong, im just trying to figure out a way round it
Click to expand...

Start by putting test data in both those tables, then once you've done that, put data in the remix table referencing that test data.
 
Remember "0" counts as an ID, select the foreign key field and delete any data in there, or the DB will look for whatever is in there, even 0, as a foreign key. MAKE SURE THERE IS NOT A ZERO, this is the default that access would enter for that foreign id number field when you make a row in remix.

db.JPG

db2.JPG
 
Smiley Man said:
i know what you're getting at but it just doesnt work, access refuses to update the vinyl and cd table from values put in the remix one
Click to expand...

It won't, because that's the way a one to many relationship works. You can't fill in the 'many' end and expect it to create a record in the 'one' end.

You need to add the data to the primary tables (vinyl and CD) before you refer to them in the 'remix' table.

You also need to ensure that the two foriegn keys are not both compulsory if it's going to be an or/both situation with their presence.
 
Smiley Man said:
i know what you're getting at but it just doesnt work, access refuses to update the vinyl and cd table from values put in the remix one
Click to expand...

As said, you will have to either create a vinyl or cd first, then assign a remix to it, or clear the vinyl/cd foreign key field to denote that there is no cd/vinyl linked to that remix.

Got anywhere you can upload your DB to? I'd happily check it's doing things right.
 
The only problem I see there is that you don't have a primary key defined for remixes. Just make remix_id that tables key.
Other than that, you dont have a default for your foreign keys so there should be no problem - just understand that when you want to put a remix to a cd or vinyl you have to go into the cd/vinyt table first and create a cd or vinyl for it to belong to. You cant belong to something that hasnt yet been made.
For example, go into cd table, add a row with id 1, and from then on you can set the cd foreign key for any remix to 1, to denote that it belongs to that CD. this is how relationships work. the 1 side MUST be created before being used.
like so:

2.JPG



as an unnecessary but helpful step, you can also add a default of todays date to be inserted into the date fields for new cd/vinyl entries. THIS IS NOT NECESSARY, JUST USEFUL.
1.JPG
 
Last edited:
*ding* ive got it now, seems my logic was out not access

if i have a tune on cd and not vinyl, is access gonna scream if i leave "vinyl ID" as blank (not 0)?
 
Smiley Man said:
*ding* ive got it now, seems my logic was out not access

if i have a tune on cd and not vinyl, is access gonna scream if i leave "vinyl ID" as blank (not 0)?
Click to expand...

Not at all, if the vinyl_id field in your remix is blank, then access knows that this remix is not on vinyl.
 
You must log in or register to reply here.
Back
Top Bottom