Can you have a shared relationship?

[piperent]

Is it possible to setup a relationship between two (2) files that shares a common field?

vehicle.vin (1) -------> (many) sales.vin

vehicle.vin (1) -------> (many) sales.tradevin

Obviously 'vehicle.vin' is the common field. In the first instance it points to all sales for a particular vehicle. In the second it points to all sales where it was used as a tradein.

I have tried setting this up using the relationships manager, but it will not allow the same field to be linked to more than one field in a subsequent table. I should stress that the database is a MySQL (ODBC) database that resides on a remote server. Informix, Oracle, Progress have no problem with linking fields multiple times, and I'm sure Access has no problem with it either, but I haven't found the means of accomplishing it, or the fact that it is not inherent to the Access DB is preventing it.

Any help will be much appreciated.

Thanks,
JP

 

[r937]

the "points to" part of your scenario is actually in the other direction

presumably, each vehicle's vin is unique, in other words, vin is the primary key of vehicle

therefore sales.vin is a foreign key, and so is sales.tradevin

so actually, those two foreign key fields "point to" the primary key in vehicle

the foreign key always references the primary key, and that is exactly how you declare the relationships using DDL

i'm not sure about how you would click and drag the fields in the Access Relationship Manager (or whatever it's called), all i know is that the relationships you describe are indeed possible

r937.com | rudy.ca

 

[GingerR]

Why not have all VINS in one field, with an additional field designating "TYPE" = "Sale" or "TradeIn"? That would be a more normalized db structure. Your table structure is what is causing you problems.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[r937]

ginger, if the sales table records the sale of vehicle A, and vehicle B was used as a trade-in for that sale, how would you represent this? two rows? then what wouyld you use as the PK for this?

i think one row with two foreign keys is the correct (normalized) structure for the sales table

r937.com | rudy.ca

 

[piperent]

Lot of food for thought here. SQL tends to be a little overpowering at times, what with all the primary key, foriegn key, normalized data, etc. In all, what I am trying to do is a simple double access of the vehicle file; once to match the sales table record, and then a second time to locate the vehicle data for the traded in vehicle. Sounds simple enough, but then I try to put all that together into a 'select' command and everything goes to pot.

I guess this must be a complex query for Access because when I try to build the 'query' to accomplish this, it comes back with errors about 'duplicate' table field names in the 'query'. Well, of course there is going to be duplicated names because when I include that second vehicle access it ties it in as 'vehicle_1' which now has a field name 'vehicel_1.vin'. This, then matches 'vehicle.vin' along with several other fields. ;-)

It would be possible to create a 'trade in' table then use that for any information regarding trade ins, but, since the trade in becomes just another vehicle to sell and would have to be created back into the 'vehicle' file, this would be self-defeating and redundant.

I'll keep after it, and it will probably come to me one night in a vision.

Thanks for your help, and if you have any further insight, ideas, or suggestions, let'em fly. I'm game for anything.
Got nothing better to do on Christmas anyway.

Thanks,
JP

 

[r937]

oh, i thought you were asking about "setting this up using the relationships manager"

i never bother with the relationships manager because i don't build access apps like that, however, i have written thousands of queries, and the query you want is easy, you just have to refer to the vehicle table twice, using table aliases -- you were really close with your idea of "double access to the vehicle file" and probably just weren't aware of the need for table aliases

paste the following into a new query SQL View and then switch to Design View to see the relationships

select sales.vin
     , sales.tradevin
     , S.model     as sale_model
     , S.modelyear as sale_modelyear
     , T.model     as trade_model  
     , T.modelyear as trade_modelyear  
  from sales
inner
  join vehicle as S
    on S.vin = sales.vin  
left outer
  join vehicle as T
    on T.vin = sales.tradevin

S and T are table aliases

note the use of column aliases in the SELECT to distinguish which vehicle they refer to

INNER JOIN from sales to S because there would not be a row in sales for an unknown vin, so sales.vin should be defined as NOT NULL

LEFT OUTER JOIN from sales to T because not every sale has a tradein vehicle, so sales.tradevin should be defined as NULL


r937.com | rudy.ca

 

[piperent]

That's great! Just what I was looking for. I'll try it out. I was actually using the design query 'what-ever that is' (manager, or whatever) to augment the files to be accessed, and it was creating the aliased file (vehicle_1 in my example), but then it didn't seem to understand what it actually built because it would give me those errors. So I just figured it was me not knowing how to put all that stuff together.

Your idea makes a lot more sense and is more in line with the other SQL commands I've been exposed to.

I'll throw you a star just because you, myself, and maybe 'Ginger' seem to be bored enough on a holiday to actually try to do something productive.

Thanks so much.
JP