3 tables

  • Hi,

    Up until now i have used excel to manage orders that are exported into csv files. There are three files created, Person, Orderlines and Orders with a field that links the Person/orders and another field that links the Orderlines/orders files.

    I've imported these three files into access and now have three tables in access - and have linked them in relationships on the fields above.

    And that's really as far as I've got this time. I've tried before but don't seem to be able to get the data to show as I would like it.

    I wary of saying 'I want it to do this and this' as I don't think it's fair to ask here for someone to write it for me, but I'm stuck as to where to go next. I'll give a brief description on what I want so that someone might be able to point me in the direction of a previous post that might help, or a tutorial that will help me do what I want (trouble with tutorials is they teach me to do something unrelated to what i want!).

    I want a form that shows all the orders by Order No, Person, Total Cost, VAT, Shipping

    On any of these lines I want to be able to click on a person and that will open up another form that shows that person's details and in a window below, all the orderlines for that person by order number (and lists of any other orders that person has had).

    I want to bring in another table that shows the cost price for all products so I can then show the profit figure for each order and by month/year etc.

    Vat amounts by month

    On the system I use, someone can enter Mr J Smith and the next time John Smith and although all other details are the same, the contact ID will be a new one so I need a method of searching for duplicates like this and saying 'this person is this person'.

    Hum... bit of a longer post than I originally thought but if anyone can point me in the right direction then Thank you!

  • There is a nice example of this type of form in the Northwind sample database that comes with Access. (mine is located in C:\Program Files\Microsoft Office\Office10\Samples).

    As to your second question (if I understand it correctly), I would concatenate the name with whatever other field or fields will create a unique key field for searching.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!