Results 1 to 3 of 3
  1. #1
    Join Date
    May 2016

    Question Unanswered: Trying to use Relationships to See what I can Build with my Inventory

    The answer to this may be very simple and I might be over thinking what I am trying to do so please bear with me.

    The best way for me to explain what I am trying to do is to use the example of cars.

    I have one spreadsheet that has a list of all of the inventory needs of my repair shop. So if I have 5 cars that need a door replacement for each car. The spreadsheet will have a line item for each car door that I need. It includes information such as the make, model, color, and part number of the car. So I have this really nice inventory of what I need base on the part number with full descriptions.

    In addition to that I have another spreadsheet that tells me what my inventory has on hand in the shop. For an example, it will have a separate line item for every car door I have in inventory along with columns for it's make, model, color, and part number.

    Here's what I would like to do. I want to be able to link my spreadsheet that has a list of all of my inventory, with the spreadsheet that has a list of all of my needs, so that I can see all of the work I can do! Is this possible?

    Mind you, I am not just talking about car doors when I get down the broader scheme of things. There are many other parts, such as windows, tires, and other parts I would like to match. You get the idea. But I would be happy if it just worked for one part too.

    It is not necessarily a "relationship" I am looking for between the two sheets, but more so a matching inquiry based on compatibility between customer needs and what I have in my inventory and matching them based on multiple criteria, in this case, make, model, color, part number.

    I don't mind if the output showed multiple matches (for instance if I had only 5 car doors, but the system were to show me 50 possible cars I could repair with them based on the matching criteria).

    Any help on relating the two spreadsheets is appreciated.

    Thank you!
    Last edited by WildSpreadSheets; 05-15-16 at 13:03.

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 23
    Stop using spreadsheet. Use Access.

    you can have the tCar table
    tInventory table (needs where Qty=-1)
    tWork table (what each car needs done.)

    Access uses autonum field the helps joining the car with the parts.
    Inventory wouldn't need a relation, but it has upkeep with Qty.

    import all your xl data and start building the master/child forms to tame this beast.

  3. #3
    Join Date
    May 2016
    Provided Answers: 4

    As ranman256 told you, use MS Access as otherwise you should recreate database behavior in Excel sheet:

    1. Firstly it's no so efficient as a database which is optimized for making queries with SQL and with VBA, Access is extremely powerful
    2. Secondly, I developed such project for a client, never again, it's absolutely dreadful and impossible to support for the futur!!
    Last edited by informer; 05-26-16 at 13:18.

Posting Permissions

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