Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    two database interacting (2000)

    Good day all,

    Here is my problem. I have two pieces of software that are based on Microsoft Access. One is a Point of Sale program, the other and Inventory program. They are not linked or combined in any way. At present I can print a report that will tell me how many of an item I sell each day from the POS program. Then manually enter those numbers into the Inventory program. The inventory program sees the items as recipes made up of different ingredients. It then will subtract the correct amount of each ingredient from the total inventory.

    Both databases use the same item numbers. So when I print the report out from the POS software I have it in the same order as the form that I input the items sold.

    I want to do away with the manual input of items sold daily. I would like to create an routine that at the end of business each day I could have the two databases "talk" to each other and have the items sold entered into the Inventory database thereby making manual entry a thing of the past.

    Any ideas on how to do this in a simple manner?

    Thanks,

    This must be a daunting task because neither company has been able to create a way for me to do this. So I have decided to try to do it myself.

    Bret

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: two database interacting (2000)

    For starters, you should be able to create a link table (one or more) in your inventory db which "sees" the relevant table(s) in the POS db. From there you can build some queries to select the data you want out of those tables and format them so that they append the items sold (using an append query) to the inventory table(s) (or adjusts values in the inventory table(s) using an update query).

    Depending on how you need to "have the items sold entered into the Inventory database" you may be able to do this just with update and/or append queries. If the updates are more complex, you may have to resort to VBA coding.

    Hope this provides a start, anyway.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: two database interacting (2000)

    >>This must be a daunting task because neither company has been able to create a way for me to do this. So I have decided to try to do it myself.<<

    Are these 2 separate companies on 2 separate computer systems? If they are on 1 system, then as Tom said, you can create linked tables in either database to see the data in the other database.

    If separate systems, you will have to export data from the first system, then import it into the 2nd system. Not particularly difficult.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: two database interacting (2000)

    The two pieces of software are on two separate computer systems. Is that what you mean? I started working on this last night. I created a third database made up of linked tables from the two pieces of software. I am having some difficulty figuring out which tables do what. I know what data I need from the POS software. I created a query that simply gives me the sum of every item on every day. That's about as far as I have gone.

    Does it appear that I am on the right track? I then thought that I would figure out which table they use in the Inventory software to reduce the inventory when I manually enter the items.

    What would be great is to have the query appending the inventory totals constantly. That way I could look at any time during a day and determine how many cases of "X" I have at any moment. That's what I really wanted from the POS software to begin with. I need a recipe based inventory system.

    Thanks for all the help. After two years I just tired of waiting on the programmer to do it for me.

    Bret

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: two database interacting (2000)

    Well I'm a little confused. In original post you said that you had 2 systems based on Access, but here you are saying you don't know what some of the tables do? Do you mean that these aren't your Access applications?

    If the 2 applications are on separate, unconnected systems, then you will have to import the data you need from the POS system to the REcipe system. Determine what info you need to see in the Recipe system, then create a query in the POS system that creates that information. Export this query to a .txt file, then transport it over to the other system. You can import or link to this text file in Recipes to get the info you need.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: two database interacting (2000)

    Is it possible that it might be simpler to just add a table or two to the POS database and create my own recipe based inventory? I have used these two pieces of software and am more or less committed to the POS software. The company that created the Inventory program is no longer around. So I can't get any support or should I move the program to another computer I can't get the unlock code either.

    If building a recipe based inventory within the POS is possible it might be better to take that route.

    Thanks again,

    Bret

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: two database interacting (2000)

    I could confuse Einstein. What I mean is there are two databases, POS and Inventory. The inventory one is old enough that when I try to look at it with Access 2000 I am prompted that to make changes I will have to convert the database.

    The programs are on a networked system so access to the data is no problem. The problem I have when I look at the tables in the Inventory software is, I'm not sure which table they use to carry out the updating of the inventory by recipe. So I am not sure where the query from the POS system should send the results.

    Did this help any?

    Bret

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: two database interacting (2000)

    >>Is it possible that it might be simpler to just add a table or two to the POS database and create my own recipe based inventory? I have used these two pieces of software and am more or less committed to the POS software. The company that created the Inventory program is no longer around. So I can't get any support or should I move the program to another computer I can't get the unlock code either.<<

    Given the circumstances, it probably would be better in the long run to keep inventory in your system. Of course, I don't really know the extent of the situation or all the factors involved, but I can't see how relying on an unsupported system would be in your client's best interest in the long run.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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