Results 1 to 8 of 8

Thread: access query

  1. #1
    Join Date
    Jun 2006
    Posts
    6

    Unanswered: access query

    Hello. This is a simple question for a database expert, which I'm not. I have a table of clients and a table of products. Each client will have multiple products. My client wants me to create a web page that displays the clients and a list of their products.

    I can't figure out how to do that. In the table there is a customer entry for each product, so the customer might appear 30 times.

    How do I do what my client wants? Any help is appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if each customer product entry is already related to the customer that owns it, you're done as far as database design is concerned

    all (!) you have to do now is build the web page
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2006
    Posts
    6

    reply

    Okay, I guess what I don't understand is how to pull the data out and present it so that it shows one customer with multiple product listings. That's what I'm confused about. I'm sure it's done all the time, but I can't figure it out.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select customer_name
         , product_id
         , product_desc
      from customers
    inner
      join products
        on products.customer_id = customers.id
     where customers.id = 937
    order
        by customer_name
         , product_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how have you identified which products are sold to which customers
    how do you handle the same product sold to more than one customer

    in access the classical way of handlng hte disply of this is to use a subform to contain the products (design the subform first - placing/plonking feildds as required, then design the top (master / parent) from next

    include the sub report using the approrpriate wizard form the design toolbar amking sure that you associate the customer with the means by which you have identified the products being sold to that customer

    normally I'd expect to see a third table which carries a pointer that associaties a specific product(s) with a specific customer(s)
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2006
    Posts
    6
    I did create a third "link" table that has 2 primary keys and is related to the 2 tables. I'm just trying to figure out how to show 1 member's profile with multiple products using an sql query on the web.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select <mycolumnlist> from <mycustomertable>
    join <mycrossreftable>.<MyCustomerID>=<mycustomertable> .<MyCustomerID> join <myProducttable>.<MyProductID>=<mycustomertable>.< MyProductID>
    where <mycustomertable>.<MyCustomerID>=<myparameter>
    order by <mysortorder>
    ...or something like that.....
    joins are preferable to using a where clause (for no other reason thant its the preferred SQL route, you could use a where clause if you sihed)
    select <mycolumnlist> from <mycustomertable>
    Where <mycrossreftable>.<MyCustomerID>=<mycustomertable> .<MyCustomerID> AND <myProducttable>.<MyProductID>=<mycustomertable>.< MyProductID>
    AND <mycustomertable>.<MyCustomerID>=<myparameter>
    order by <mysortorder>

    this assumes that you have an intermediate table (i prefer to call a crossreference (xref) table, but its also called by lots of other names)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2006
    Posts
    6

    thanks

    Thanks to everyone for helping me. I'll see if I can get this to work.

Posting Permissions

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