If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database design question!
Hi,
I've got a database design dilemma and was wondering if I could get some thoughts on it. I have a Goods table that tracks items that are imported from a country and exported. for Example a typical record reads: [Import Country] - [Export Country] - [Description] Britain - USA - Woodenbox I have a table called country which lists all available country choices and country codes. I have [Import Country] set as the foreign Key / linked to the primary key of the Country table. This works fine, as expected and as required. My Dilemma comes from the [Export Country] field. The choices of which are the exact same as the [Import Country] but access wont allow me to define two relationships to the primary key of the country table linking to different fields in the goods table. Only one or the other. Whats the best way around this? Have a table each, ImportCountryList and ExportCountryList? It could be a nightmare keeping the tables the same as when a new record's inserted into one I have to ensure its inserted into the other etc. This leads to data repetition (bad) etc which is making me think I'm going about this the wrong way. Ideally I would like referential Integrity for both field in the Goods table (Import Country and Export Country). I'm hoping there's something simple I'm overlooking... Thanks Asi |
#2
|
|||
|
|||
In line...
wrote in message oups.com... Hi, I've got a database design dilemma and was wondering if I could get some thoughts on it. I have a Goods table that tracks items that are imported from a country and exported. for Example a typical record reads: [Import Country] - [Export Country] - [Description] Britain - USA - Woodenbox I have a table called country which lists all available country choices and country codes. I have [Import Country] set as the foreign Key / linked to the primary key of the Country table. This works fine, as expected and as required. My Dilemma comes from the [Export Country] field. The choices of which are the exact same as the [Import Country] but access wont allow me to define two relationships to the primary key of the country table linking to different fields in the goods table. Only one or the other. Don't know how you are attempting to create the relationship but Access will allow this. Go into the relationship window and add the Country Table twice. Access will automatically name the second virtual country table Country_1. Create a relationship between ImportCountry and the Country table and a relationship between ExportCountry and Country_1. Whats the best way around this? Have a table each, ImportCountryList and ExportCountryList? It could be a nightmare keeping the tables the same as when a new record's inserted into one I have to ensure its inserted into the other etc. This leads to data repetition (bad) etc which is making me think I'm going about this the wrong way. Ideally I would like referential Integrity for both field in the Goods table (Import Country and Export Country). I'm hoping there's something simple I'm overlooking... Thanks Asi |
#3
|
|||
|
|||
Sorry,
I meant to add that I've tried adding in the table twice in the relationship window. While it solves the relationship problem it messes up the queries. In the query builder If I add in the goods table, followed by the country codes it creates two relationships from country codes to the goods table. Where by running a select * on the Goods table returns nothing. Adding in country codes again twice just leaves country_codes_1 with no relationship defined though there is one in the acual relationship diagram.... |
#4
|
|||
|
|||
Hey Asi.
Asi wrote: I've got a database design dilemma and was wondering if I could get some thoughts on it. I have a Goods table that tracks items that are imported from a country and exported. for Example a typical record reads: [Import Country] - [Export Country] - [Description] Britain - USA - Woodenbox I have a table called country which lists all available country choices and country codes. I have [Import Country] set as the foreign Key / linked to the primary key of the Country table. This works fine, as expected and as required. My Dilemma comes from the [Export Country] field. The choices of which are the exact same as the [Import Country] but access wont allow me to define two relationships to the primary key of the country table linking to different fields in the goods table. Only one or the other. [snip] Wrong. You can create your two relationships, separately, just try and read the message and click the apropriate button (No). Access will display one of the tables twice, one with "_1". -- Regards, Wolfgang |
#5
|
|||
|
|||
In the query builder add the goods table.
In the query builder add the country codes table. It will create two relationships -- delete one of them In the query builder add the country codes table again. Re-create the relationship that you deleted in the previous step only using the country_codes_1 table. When you click on SQL view the FROM clause of the query should look something like (you field and table names probably will be different): FROM (tblCountry INNER JOIN tblImportExport ON tblCountry.CountryName = tblImportExport.ImportKey) INNER JOIN tblCountry AS tblCountry_1 ON tblImportExport.ExportKey = tblCountry_1.CountryName; wrote in message oups.com... Sorry, I meant to add that I've tried adding in the table twice in the relationship window. While it solves the relationship problem it messes up the queries. In the query builder If I add in the goods table, followed by the country codes it creates two relationships from country codes to the goods table. Where by running a select * on the Goods table returns nothing. Adding in country codes again twice just leaves country_codes_1 with no relationship defined though there is one in the acual relationship diagram.... |
#6
|
|||
|
|||
Thanks for the feedback guys. It was because I was having to do extra
fiddling in the query design window (as mentioned by Bill) that I thought maybe I was doing something wrong. But if thats the way to go, and I'm not doing anything thats considered bad by design or a no-no, then I can dig it. Rgds Asim |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Question regarding design of tables for inventory database | Dee | Database Design | 1 | February 11th, 2005 09:01 AM |
Encrypt AccesS File? | milest | General Discussion | 2 | February 9th, 2005 07:58 PM |
Access Error Message when opening database | eah | General Discussion | 3 | January 26th, 2005 10:04 AM |
Database Window Gone | DaveB | General Discussion | 2 | July 29th, 2004 12:24 AM |
Database design question | gil | General Discussion | 3 | June 13th, 2004 04:31 PM |