|
| |
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
 |
RE: multiple databases with fields - 12/21/2005 16:14:16
Options.aa is just symbols, like: "n[","tp", "zI", "m3"
Options.bb is the desriptions: "(ABS) ANTI-LOCK BRAKING SYSTEM", "0NE TOUCH 4X4", "0VER SIZE OFF ROAD TIRES", "1 OWNER"
VEHICLES.cstocknum just contains the stock numbers
VEHICLES.o1 has entries corresponding to Options.aa: "n[","tp", "zI", "m3"
|
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/21/2005 16:53:49
and VEHICLES.o2 links to the OPTIONS.aa???
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/21/2005 17:00:51
Yes, VEHICLES.o1 thru VEHICLES.o8 all relate to OPTIONS.aa
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/21/2005 17:09:08
Ok, the design of the database is horribly flawed. By that I mean it will take a LOT of work to maintain.
If you want to link 1 record (VEHICLE) to multiple OPTIONS then you would be best off creating an intermediary table to link them. Something like this
VEHICLE
Id Description
1 Aston Martin
2 Monster Truck
3 General Lee....
OPTIONS
Id Item
1 Airbags
2 Stereo
3 Window wipers...
VEHICLESrelOptions
VehicleId OptionId
1 1
1 2
2 2
2 3
5 1
5 16...
Rule of thumb - if you are duplicating fields(columns) within a table because you have a 1 to many relationship, then it's probably best to move that information out of the table onto a new table.
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/22/2005 9:45:39
Understood and agreed, however, I have to make it work with what I have. Also, it will be hard to create an intermediary table since the main database (Vehicles) will be changing and will need to be uploaded on a daily basis.
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/22/2005 15:34:43
Ok then, I can think of 2 options.
1. When the file is uploaded you run a collection of statements that one by one update each field using a JOIN and UPDATE.
2. Use regex to parse the file, match and replace the codes with the full descriptions.
Number 1 is probably easiest.
UPDATE VEHICLES
SET o1 = o.bb
FROM VEHICLES v
INNER JOIN OPTIONS o
ON o.aa = v.o1
UPDATE VEHICLES
SET o2 = o.bb
FROM VEHICLES v
INNER JOIN OPTIONS o
ON o.aa = v.o2
UPDATE VEHICLES
SET o3 = o.bb
FROM VEHICLES v
INNER JOIN OPTIONS o
ON o.aa = v.o3
continue to number 8
then you can just run this simple select statement
SELECT v.cstocknum, v.o1, v.o2, v.o3, v.o4, v.o5, v.o6, v.o7, v.o8"
FROM VEHICLES v
ORDER BY v.cstocknum ASC
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/22/2005 17:14:20
1 sounds brilliant!
let me try...
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/22/2005 17:27:48
This is my query, but it produced an error:
UPDATE VEHICLES SET o1 = OPTIONS.bb FROM VEHICLES INNER JOIN OPTIONS ON OPTIONS.aa = VEHICLES.o1
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 12/22/2005 17:32:45
When using a JOIN with an UPDATE you have to use aliases because you've already referred to the table at the beginning of the query, so this should read (at least)
UPDATE VEHICLES
SET o1 = OPTIONS.bb
FROM VEHICLES v
INNER JOIN OPTIONS
ON OPTIONS.aa = v.o1
but I'd be inclined to use aliases for all of the right-hand half of the query. Beware! You can't use aliases on the lefthand side of an UPDATE query (just incase you ever try and can't work out why it failed - I've been there! :)
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 12/23/2005 11:45:15
Still getting an error on
fp_sQry="UPDATE VEHICLES SET o1 = OPTIONS.bb FROM VEHICLES v INNER JOIN OPTIONS ON OPTIONS.aa = v.o1"
Also tried
fp_sQry="UPDATE VEHICLES SET o1 = OPTIONS.bb FROM VEHICLES v INNER JOIN OPTIONS ON OPTIONS.aa = o1"
And
fp_sQry="UPDATE VEHICLES SET o1 = OPTIONS.bb FROM VEHICLES INNER JOIN OPTIONS ON OPTIONS.aa = o1"
Not really sure what you mean with the alias stuff.
BTW, thanks for sticking with me on this.
| |
|
|
|
Sandalwood
Posts: 233
Joined: 12/18/2002
From: New Jersey!
Status: offline
|
RE: multiple databases with fields - 1/17/2006 11:47:29
Pasted Query into Access and got this message:
Syntax error (missing operator)
'OPTIONS.bb
FROM VEHICLES v
INNER JOIN OPTIONS
ON OPTIONS.aa = v.01'.
| |
|
|
|
yogaboy
Posts: 377
Joined: 5/22/2004
Status: offline
|
RE: multiple databases with fields - 1/17/2006 17:59:29
Hi,
I'm looking at my posts and thinking I wasn't getting a lot of sleep, perhaps that's why I was writing such rubbish (I'm not getting a lot of sleep now either!)
You could try this instead of my crackpot posts from earlier.
Add 8 description fields to the Vehicles table, 1 for each of the codes.
ie o1, desc1, o2, desc2, o3, desc3....
then run a delete statement to clear the previous info (since you say you don't need it?)
DELETE from Vehicles
DELETE from OPTIONS
Then add the new data to the tables.
Then you could run 8 update statements like
UPDATE VEHICLES
SET desc1 = OPTIONS.bb
FROM VEHICLES v
INNER JOIN OPTIONS
ON OPTIONS.aa = v.o1
UPDATE VEHICLES
SET desc2 = OPTIONS.bb
FROM VEHICLES v
INNER JOIN OPTIONS
ON OPTIONS.aa = v.o2
....
then you could do
SELECT stocknumber, desc1, desc2 etc
from vehicles
i've tested this on a SQL Server 2000 database and it works, so if you fiddle around a bit it I reckon it'll work. Sorry for the sloppy code above, I really wasn't getting much sleep.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
