Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25

    Unanswered: Linking two fields within table to another table

    Hi there all

    I have a table called stock and within this table there are two fields called "sale_rep_entry" and "sales_rep_exit". This information means that a stock item can come in through one sales rep and can be sold by another.

    I would like to link both of these to a table called sales_rep and field is called "sale_rep_id". Access is not liking this. I am sure that there is a way to duplicate the sales_rep table but not sure how to do this.

    I hope I have explained this OK.

    Thanks in advanced.

    Centipede
    Mikeg950

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select stock.item
         , rep_entry.name as entry_name
         , rep_exit.name  as exit_name
      from (
           stock
    inner
      join sales_rep as rep_entry
        on rep_entry.sale_rep_id = stock.sale_rep_entry
           )
    inner
      join sales_rep as rep_exit
        on rep_exit.sale_rep_id = stock.sale_rep_exit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25

    Creating a dupilcate table

    Hi there r937

    Thanks very much for your reply. I think it may have been a little to advance for me at this stage or I'm not sure how to apply it. I have done a screen dump of my ERD to give you a better understanding of what I am trying to achieve.

    Different sales reps are able to bring stock into the system as inventory. And a different sales rep can sell an stock item. I need to capture who brought it in and who sold it. Also you will see sales_rep table linked to transport order to capture who ordered a transport to be done.

    I have worked out that you are able to create a duplicate by simply copy and paste but when you update one it will not update the other. How would you acheive this.

    Thanks again Mikeg950
    Attached Thumbnails Attached Thumbnails ERD.jpg  
    Mikeg950

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, well, i had to guess at some of the field names, so naturally i got a couple wrong, eh
    Code:
    select stock.stock_num
         , rep_entry.sale_rep_lastname as entry_name
         , rep_exit.sale_rep_lastname  as exit_name
      from (
           stock
    inner
      join sales_rep as rep_entry
        on rep_entry.sale_rep_id = stock.sales_rep_entry
           )
    inner
      join sales_rep as rep_exit
        on rep_exit.sale_rep_id = stock.sales_rep_exit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25

    Creating Dupicate table

    Thanks again

    I'm not 100% sure how to apply this code. Do I use this to create a second table through a query?

    Or is this appied in the table properties some where. As you can see I am very rusty at this.
    Mikeg950

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    go to Query Design View

    select SQL View

    paste the query

    click the exclamation mark to run it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25

    Duplicate table

    Hay

    Thanks very much for that. I couldn't believe when I pasted it in as a query and it worked first go. I see where you where coming from and I learnt about the technique on using inner joins. That query is going to come in handy.

    I dont think I explained my self good enough. The sales_rep table is linked to stock.sales_rep_entry. I needed a table the exact same to stock.sale_rep_exit. From the idea that you gave me I created a query called sales_rep(1) and I am setting up test data to see if it works.

    The auctual relationship from stock.sale_rep_exit to sales_rep(1) seems to be a straight line and not indicating the type of relationshipi.e. one to many so I'm not to sure what is happening there. I will do some more investigation.
    Thanks so much for your time.

    Regards

    Mike
    Mikeg950

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in your diagram, you had sales_rep and sales_rep(2)

    now you say you "created a query called sales_rep(1)"

    what's going on? there should only be one table, sales_rep

    you notice that my query refers to this table twice, but it's still just the one table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25

    Dupicate table

    R937
    I have posted the latest ERD.
    Let me explain what my original aim was. When a new stock item is entered you select the sales rep that brought / traded this in. When the item is sold I want to be able to enter the stock number into the form and then select who sold the item. This will enable me to perform sales reports etc.

    I would have liked to have linked stock.sales_rep_entry and stock.sales_rep_exit to the same table [sales_rep] but Access would not allow me. [Or does it?] Hence why I was triing to duplicate the table and have the secondary table [sales_rep1] update automatically when new entries are entered into the primary table [sales_rep].

    I think I may have achieve this by the idea you gave me, by creating a query and treating this as a table. [Not sure if this will work?]. As you can see from the ERD I have triied joining this but it is not a true 1 to many relationship.

    I hope this gives you a better picture of what I am triing to do. Am I going around this around the wrong way?

    Thanks

    Mikeg950 [sorry about the long story]
    Attached Thumbnails Attached Thumbnails ERD2.jpg  
    Mikeg950

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mikeg950
    I would have liked to have linked stock.sales_rep_entry and stock.sales_rep_exit to the same table [sales_rep] but Access would not allow me. [Or does it?]
    yes, it does -- have a look at the query in post #4, i do it there


    Quote Originally Posted by mikeg950
    I think I may have achieve this by the idea you gave me, by creating a query and treating this as a table. [Not sure if this will work?].
    you don't need to create a query and treat it as a table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2003
    Location
    Hamilton, NZ
    Posts
    25

    Wink Dupicating the table

    R937

    Thanks very much for your time. Leave it with me and I will start populating the database and see if I can get things going. I am currently getting Access installed at work so I can develop on this at work as well. It's going to make my easier geting this going.

    Currently I have a series of spread sheets.

    I think I have the picture now.
    Mikeg950

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •