lookup tables
- Thread starter ozlander
- Start date
Should this be appended to another thread somewhere?
Mile-O
Back once again...
- Local time
- Today, 11:49
- Joined
- Dec 10, 2002
- Messages
- 11,305
It's just that you mention Questions 3 and 4 and there's nothing of a question 1 or 2. I don't even know what 3 and 4 are.
However, it's best to put your lookups in separate tables. If you put them all in the same table then you suffer from lists of varying sizes meaning that you'd have loads of redundant (i.e. blank) cells.
Use an autonumber as the primary key.
If the subject of the table is, for example, cities then the appropriate table would be:
CityID (Autonumber)
City (Text)
In other tables where you want the city then you would define a field called CityID (Number) and use this.
What was question 3?
However, it's best to put your lookups in separate tables. If you put them all in the same table then you suffer from lists of varying sizes meaning that you'd have loads of redundant (i.e. blank) cells.
Use an autonumber as the primary key.
If the subject of the table is, for example, cities then the appropriate table would be:
CityID (Autonumber)
City (Text)
In other tables where you want the city then you would define a field called CityID (Number) and use this.
What was question 3?
If I haven't got the tables right ... the the rest is irelevant! I thought I had a handle on it, but I don't ... because it doesn't work as it should! I would really appreciate any assistance you can provide ... this is my first, real life db ... and I know the tables are incorrect ... but I really do not understand ... I should, but I don't ... I don't know if the lookup tables should be linked to the others or just sit there in isolation ... if it be the latter then it /they doesn't/don't comform to the normalisation stuff ... I am lost totally!
Regards
Oz
Regards
Oz
#3 was http://www.access-programmers.co.uk/forums/showthread.php?t=76154
but I have that file ... I figured if I went back to the beginning I may have a chance to get it correct ...
Regards
Oz
but I have that file ... I figured if I went back to the beginning I may have a chance to get it correct ...
Regards
Oz
Supplier Table
SupplierID
SupplierName
Organisation ( y/n)
ContactName
UnitNumber
SreetNumberFrom
StreetNumberTo
StrretName
StreetType (Combo... Lookup)
Suburb (Combo ... lookup)
PostCode (Combo...lookup)
State (combo...lookup)
DOB
IdentificationNumber
IdentificationType
ItemSerial Table
ItemSerialID
ItemID
SerialNumber
SupplierID
DateOfPurchase
Price
IdSupplied (yes/no)
ItemClassGeneral
ItemID
ItemClass
ItemDescription
StreetType Table
StreetTypeID
StreetFullType
StreetAbbreviation (<<<<<<<<this is a lookup)
IdentificationType Table
IdentificationID
IdentificationType
PostCode Table
PostCodeID
Suburb (<<<<<<<<this is a lookup)
PostCode (<<<<<<<<this is a lookup)
State Table
StateID
State (<<<<<<<<this is a lookup)
I think that's the way it should be ... but now I am not sure
Regards
Oz
SupplierID
SupplierName
Organisation ( y/n)
ContactName
UnitNumber
SreetNumberFrom
StreetNumberTo
StrretName
StreetType (Combo... Lookup)
Suburb (Combo ... lookup)
PostCode (Combo...lookup)
State (combo...lookup)
DOB
IdentificationNumber
IdentificationType
ItemSerial Table
ItemSerialID
ItemID
SerialNumber
SupplierID
DateOfPurchase
Price
IdSupplied (yes/no)
ItemClassGeneral
ItemID
ItemClass
ItemDescription
StreetType Table
StreetTypeID
StreetFullType
StreetAbbreviation (<<<<<<<<this is a lookup)
IdentificationType Table
IdentificationID
IdentificationType
PostCode Table
PostCodeID
Suburb (<<<<<<<<this is a lookup)
PostCode (<<<<<<<<this is a lookup)
State Table
StateID
State (<<<<<<<<this is a lookup)
I think that's the way it should be ... but now I am not sure
Regards
Oz
Mile-O
Back once again...
- Local time
- Today, 11:49
- Joined
- Dec 10, 2002
- Messages
- 11,305
Why would an organisation have a date of birth?
Does the post code not belong to a state?
It would appear that you are trying to relate the wrong fields. For example, in the Suppliers table you should name the fields - i.e. StreetType as StreetTypeID and set it to a Number data type. You then link it between the StreetTypeID field in the street types table. It looks as if you have it defined as a text field in the suppliers table and are trying to link to the text in the street types table.
Does the post code not belong to a state?
It would appear that you are trying to relate the wrong fields. For example, in the Suppliers table you should name the fields - i.e. StreetType as StreetTypeID and set it to a Number data type. You then link it between the StreetTypeID field in the street types table. It looks as if you have it defined as a text field in the suppliers table and are trying to link to the text in the street types table.
Does the post code not belong to a state?
PostCodes do belong to states... they are marked by the first digit
If I do as you suggest and put StreetType as StreetTypeID in the supplier table and set it to a Number data type ... how do I see the data? May be I am thinking too much like a ss ... darn.. I thought I wasn't ... but...
All I want is to use it as a lookup/combo
Could you explain how it works (yet again) ... my thinking is that I need to see the data ... yet I know it doesn't work that way ... I think I have missed the point completely ... sorry ... but I will try, esp if I undersatnd
Ty
Regards
Oz
PostCodes do belong to states... they are marked by the first digit
If I do as you suggest and put StreetType as StreetTypeID in the supplier table and set it to a Number data type ... how do I see the data? May be I am thinking too much like a ss ... darn.. I thought I wasn't ... but...
All I want is to use it as a lookup/combo
Could you explain how it works (yet again) ... my thinking is that I need to see the data ... yet I know it doesn't work that way ... I think I have missed the point completely ... sorry ... but I will try, esp if I undersatnd
Ty
Regards
Oz
Mile-O
Back once again...
- Local time
- Today, 11:49
- Joined
- Dec 10, 2002
- Messages
- 11,305
If you do it as I say then you will put stuff into the field such as 1 or 4 or 10 which would correspond to the ID of your other table.
In fact, rather than directly selecting number, select the field's data type as a Lookup field and follow the wizard's instructions.
Select the ID and typical value of the field into the wizard.
Have a look at this lookup join in the example - switch to the Lookup tab too.
In fact, rather than directly selecting number, select the field's data type as a Lookup field and follow the wizard's instructions.
Select the ID and typical value of the field into the wizard.
Have a look at this lookup join in the example - switch to the Lookup tab too.
ok.... the continents have many countries ... I understand that bit ... why is it a number ... shouldn't it be text ... ohhhhhhhhh ... the ID bit ...then assign the column ... der Oz
ok so postcodes, suburbs and states belong together .. .. and should be related via the ID number rather than the text ... ...I'll try...
ty ... but it looks like I'll be back ... lol
Regards
Oz
ok so postcodes, suburbs and states belong together .. .. and should be related via the ID number rather than the text ... ...I'll try...
ty ... but it looks like I'll be back ... lol
Regards
Oz
Hi again
Although, the lookup wizard is listed, it doesn't work ... so I typed the required 'stuff' under the lookup tab ... and attempted the relationships manually ... it now works ... ty
I'd like to show you the relationships so far ... so have attached a jpg ... if it should be on another thread, I apologise in advance
Regards
Oz
Although, the lookup wizard is listed, it doesn't work ... so I typed the required 'stuff' under the lookup tab ... and attempted the relationships manually ... it now works ... ty
I'd like to show you the relationships so far ... so have attached a jpg ... if it should be on another thread, I apologise in advance
Regards
Oz
Attachments
Like this one >>>> http://www.access-programmers.co.uk/forums/showthread.php?t=76238
It is sort of related ... but then again ...
Regards
Oz
It is sort of related ... but then again ...
Regards
Oz
Mile-O
Back once again...
- Local time
- Today, 11:49
- Joined
- Dec 10, 2002
- Messages
- 11,305
ozlander said:
Yes. But you already have the suburb in the post code table. Having it in the suppliers table is duplication of your data. Since your suppliers table has a postcode you can link the two tables on postcode in a query and then select the suburb from the post code table. They query will align them correctly.
You make it sound as if you have bound tables to your forms - you should ensure your forms are bound to queries.
hmmm...ok...I'll try that... ty
However, what I now have realised is that I may have a real problem with the combos ... because if I export the data via a text file ... all the combos show up as numbers as in IDs... so may be I should not use the combos at all ... so again I am lost... what do you think?
Regards
Oz
... and darn, now I have to apologise for double posting ... sorry ... http://www.access-programmers.co.uk/forums/showthread.php?t=76238
However, what I now have realised is that I may have a real problem with the combos ... because if I export the data via a text file ... all the combos show up as numbers as in IDs... so may be I should not use the combos at all ... so again I am lost... what do you think?
Regards
Oz
... and darn, now I have to apologise for double posting ... sorry ... http://www.access-programmers.co.uk/forums/showthread.php?t=76238
