Attempting to clean up a db --- need some help...

[jamesfreddyc]

Ok, so I have acquired a database that needs some work and I am looking for
some help. Not much thought went into the db design, so I am trying to do it
after-the-fact.

Anyway.... I am attempting to assign Table1's "Loc_AssetID" to Table2's
AssetID where Table1 and Table2 PkID's match. If you notice in my example
below, there might be 3 PkID's = 6 in Table1 while there are 2 PkID's in
Table2 (Many-To-Many).

Table1
Loc_AssetID PKID
17750 6
17751 7
17752 8
18007 6
18008 6

Table2
AssetID PkID
6
6
7
8


I tried an UPDATE query on Table2 to test to see what would happen. The SQL
below assigned Loc_AssetID 18008 to all 2 matching records in Table2.

UPDATE INSPECTIONS LEFT JOIN ASSETS_INSPECTIONS ON INSPECTIONS.PKID =
ASSETS_INSPECTIONS.PKID SET INSPECTIONS.ASSETID =
[ASSETS_INSPECTIONS].[LOC_ASSETID]
WHERE (((INSPECTIONS.PKID)=6));

The whole point to my exercise here is to be able to perform a 1-to-many
relationship between the two. The problem I have now is that it's a
many-to-many without any way to build a junction table. Or is there a way to
do this after the fact?

Thanks for any help/input!!!

j

 

[KARL DEWEY]

I am attempting to assign Table1's "Loc_AssetID" to Table2's AssetID where
Table1 and Table2 PkID's match.
I do not see how you can do it. Which of the three Loc_AssetID's would you
place in the AssetID of table2 when there is a 6 in PkID?
relationship between the two.
To set up a 1-to-many you need a field that has unique values to be the '1'
side. You must analyze your data to see if you have such so as to build a
new table with a primary key.


--
KARL DEWEY
Build a little - Test a little

Ok, so I have acquired a database that needs some work and I am looking for
some help. Not much thought went into the db design, so I am trying to do it
after-the-fact.

Anyway.... I am attempting to assign Table1's "Loc_AssetID" to Table2's
AssetID where Table1 and Table2 PkID's match. If you notice in my example
below, there might be 3 PkID's = 6 in Table1 while there are 2 PkID's in
Table2 (Many-To-Many).

Table1
Loc_AssetID PKID
17750 6
17751 7
17752 8
18007 6
18008 6

Table2
AssetID PkID
6
6
7
8


I tried an UPDATE query on Table2 to test to see what would happen. The SQL
below assigned Loc_AssetID 18008 to all 2 matching records in Table2.

UPDATE INSPECTIONS LEFT JOIN ASSETS_INSPECTIONS ON INSPECTIONS.PKID =
ASSETS_INSPECTIONS.PKID SET INSPECTIONS.ASSETID =
[ASSETS_INSPECTIONS].[LOC_ASSETID]
WHERE (((INSPECTIONS.PKID)=6));

The whole point to my exercise here is to be able to perform a 1-to-many
relationship between the two. The problem I have now is that it's a
many-to-many without any way to build a junction table. Or is there a way to
do this after the fact?

Thanks for any help/input!!!

j

 

[jamesfreddyc]

Karl,

Thanks for the input... yes, I tried a feeble attempt at salvaging what I
have to work with. Oh well...

I would like more input on how to get this all straight. Ultimately I need
to have:

Table1: LOCATIONS
AssetID (no duplicates) System (duplicates ok)
17499 DT12270-C1
17500 DT12270-C1
17501 DT12275-C9
17502 DT12275-C4

Table2: INSPECTIONS
InspID (no dups) Loc_AssetID (dups ok) PhotoID
1 17499 p900
2 17499 p901
3 17500 p909
4 17501 p910
5 17501 p913


This is what I have to work with now:

Table1: LOCATIONS
AssetID (no duplicates) System (duplicates ok)
17499 DT12270-C1
17500 DT12270-C1
17501 DT12275-C9
17502 DT12275-C4

Table2: INSPECTIONS
InspID (no dups) System (dups ok) PhotoID
1 DT12270-C1 p900
1 DT12270-C1 p901
4 DT12275-C9 p909
4 DT12275-C9 p910


As I mentioned, this would allow me to have a functioning one-to-many relate
between the Locations table and the Inspections table via the AssetID.

Table1 and Table2 PkID's match.
I do not see how you can do it. Which of the three Loc_AssetID's would you
place in the AssetID of table2 when there is a 6 in PkID?
relationship between the two.
To set up a 1-to-many you need a field that has unique values to be the '1'
side. You must analyze your data to see if you have such so as to build a
new table with a primary key.


--
KARL DEWEY
Build a little - Test a little

Ok, so I have acquired a database that needs some work and I am looking for
some help. Not much thought went into the db design, so I am trying to do it
after-the-fact.

Anyway.... I am attempting to assign Table1's "Loc_AssetID" to Table2's
AssetID where Table1 and Table2 PkID's match. If you notice in my example
below, there might be 3 PkID's = 6 in Table1 while there are 2 PkID's in
Table2 (Many-To-Many).

Table1
Loc_AssetID PKID
17750 6
17751 7
17752 8
18007 6
18008 6

Table2
AssetID PkID
6
6
7
8


I tried an UPDATE query on Table2 to test to see what would happen. The SQL
below assigned Loc_AssetID 18008 to all 2 matching records in Table2.

UPDATE INSPECTIONS LEFT JOIN ASSETS_INSPECTIONS ON INSPECTIONS.PKID =
ASSETS_INSPECTIONS.PKID SET INSPECTIONS.ASSETID =
[ASSETS_INSPECTIONS].[LOC_ASSETID]
WHERE (((INSPECTIONS.PKID)=6));

The whole point to my exercise here is to be able to perform a 1-to-many
relationship between the two. The problem I have now is that it's a
many-to-many without any way to build a junction table. Or is there a way to
do this after the fact?

Thanks for any help/input!!!

j

 

[KARL DEWEY]

You might try this structure ---
Table1: LOCATIONS
AssetID (no duplicates)
17499
17500
17501
17502

Table2: SYSTEMS
System (no duplicates)
DT12270-C1
DT12275-C9
DT12275-C4

Table3: SystemLocation
System (duplicates ok) AssetID (duplicates ok)
DT12270-C1 17499
DT12270-C1 17500
DT12275-C4 17501
DT12275-C9 17502

Table4: INSPECTIONS
InspID (no duplicates) INSPECTOR InspDate
1 Bill 02/04/2007
2 Joe 03/01/2007
3 Jim 07/11/2007
4 Dan 09/21/2007

Table5: InspDetail
InspID (duplicates ok) System PhotoID
1 DT12270-C1 p900
1 DT12270-C1 p901
2 DT12275-C4 p909
3 DT12275-C9 p910

--
KARL DEWEY
Build a little - Test a little

Karl,

Thanks for the input... yes, I tried a feeble attempt at salvaging what I
have to work with. Oh well...

I would like more input on how to get this all straight. Ultimately I need
to have:

Table1: LOCATIONS
AssetID (no duplicates) System (duplicates ok)
17499 DT12270-C1
17500 DT12270-C1
17501 DT12275-C9
17502 DT12275-C4

Table2: INSPECTIONS
InspID (no dups) Loc_AssetID (dups ok) PhotoID
1 17499 p900
2 17499 p901
3 17500 p909
4 17501 p910
5 17501 p913


This is what I have to work with now:

Table1: LOCATIONS
AssetID (no duplicates) System (duplicates ok)
17499 DT12270-C1
17500 DT12270-C1
17501 DT12275-C9
17502 DT12275-C4

Table2: INSPECTIONS
InspID (no dups) System (dups ok) PhotoID
1 DT12270-C1 p900
1 DT12270-C1 p901
4 DT12275-C9 p909
4 DT12275-C9 p910


As I mentioned, this would allow me to have a functioning one-to-many relate
between the Locations table and the Inspections table via the AssetID.

I am attempting to assign Table1's "Loc_AssetID" to Table2's AssetID where

Table1 and Table2 PkID's match.
I do not see how you can do it. Which of the three Loc_AssetID's would you
place in the AssetID of table2 when there is a 6 in PkID?

The whole point to my exercise here is to be able to perform a 1-to-many

relationship between the two.
To set up a 1-to-many you need a field that has unique values to be the '1'
side. You must analyze your data to see if you have such so as to build a
new table with a primary key.


--
KARL DEWEY
Build a little - Test a little

Ok, so I have acquired a database that needs some work and I am looking for
some help. Not much thought went into the db design, so I am trying to do it
after-the-fact.

Anyway.... I am attempting to assign Table1's "Loc_AssetID" to Table2's
AssetID where Table1 and Table2 PkID's match. If you notice in my example
below, there might be 3 PkID's = 6 in Table1 while there are 2 PkID's in
Table2 (Many-To-Many).

Table1
Loc_AssetID PKID
17750 6
17751 7
17752 8
18007 6
18008 6

Table2
AssetID PkID
6
6
7
8


I tried an UPDATE query on Table2 to test to see what would happen. The SQL
below assigned Loc_AssetID 18008 to all 2 matching records in Table2.

UPDATE INSPECTIONS LEFT JOIN ASSETS_INSPECTIONS ON INSPECTIONS.PKID =
ASSETS_INSPECTIONS.PKID SET INSPECTIONS.ASSETID =
[ASSETS_INSPECTIONS].[LOC_ASSETID]
WHERE (((INSPECTIONS.PKID)=6));

The whole point to my exercise here is to be able to perform a 1-to-many
relationship between the two. The problem I have now is that it's a
many-to-many without any way to build a junction table. Or is there a way to
do this after the fact?

Thanks for any help/input!!!

j