Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

problem with primary key

webmaster220webmaster220 Member Posts: 3
[b][red]This message was edited by webmaster220 at 2005-8-9 13:34:56[/red][/b][hr]
I have a relationship between one table and another table which has given me a hard time. I found that if there were duplicate items in the primary key column in either table, those elements would be duplicated in the merged query. I tried to say the relationship between the two tables was a one - to many relationship to resolve this issue but it was still wrong. The way I solved it is to use excel instead of access. I did everything else the same except when it came time to join the two tables via the primary key that had the same data in one table that it had in the other table, I used the vlookup formula in excel, where both tables were located, to match up the transaction specific data to the main table instead of joining the tables in Access. In all cases where the join would have duplicated some transactions in the table, it no longer duplicated any transactions. I can now utilize that policy for future situations that I may encounter.



Comments

  • splansingsplansing Member Posts: 140
    : [b][red]This message was edited by webmaster220 at 2005-8-9 13:34:56[/red][/b][hr]
    : I have a relationship between one table and another table which has given me a hard time. I found that if there were duplicate items in the primary key column in either table, those elements would be duplicated in the merged query. I tried to say the relationship between the two tables was a one - to many relationship to resolve this issue but it was still wrong. The way I solved it is to use excel instead of access. I did everything else the same except when it came time to join the two tables via the primary key that had the same data in one table that it had in the other table, I used the vlookup formula in excel, where both tables were located, to match up the transaction specific data to the main table instead of joining the tables in Access. In all cases where the join would have duplicated some transactions in the table, it no longer duplicated any transactions. I can now utilize that policy for future situations that I may encounter.
    :
    :
    :
    :
    Access is not arbitrarily deciding what kind of relationship to build for you. If you have a field that is unique in two tables, it has to be one-to-one. Similarly, if you have a unique field in one table and the same field is NOT unique in another, then you can have a one-to-many relationship. Sounds like you just don't have the concepts quite straight, if you don't mind me saying. If you want a one-to-many relationship, the table that is supposed to be the "many" table must have the key in it, but it cannot be the primary key for that table. For example:

    Customers - has a CustomerID (unique, primary key for this table)
    Orders - has a CustomerID (not unique), and maybe also an OrderID (which is the primary key for the Orders table)

    You join these two tables on the CustomerID and Access will rightly determine that you are building a one-to-many relationship.
Sign In or Register to comment.