Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Location
    Calgary, AB
    Posts
    8

    Unanswered: merging data from 2 access databases

    Hi,
    I recently took a position with a large food distributor. Unfortunately, they are somewhat behind the times in that they have no integrated purchasing/inventory management resource planning software.

    1. We have a sales program that tracks all orders placed by our customers and cases shipped. I can access this data in near realtime by querying an access database.

    2. We have a purchasing program that tracks all orders placed by the purchasing team with outside suppliers. I can access this data by querying an access database.

    Now the problem. We have no inventory management program. In other words, we are completely blind. When i place an order with a supplier for 15X product ABZ they ship it to the plant and from there, we ship 12 to a distributor. We have no way to track how many product ABZ (3) remain in the warehouse.

    The solution

    We are planning a major upgrade in two years time, until then, i need to find a solution to this problem. I'm no database expert, in fact, i'm taking on this responsibility to make my job easier and so far, i've made a considerable impact in numerous areas in the month i've been with this company.

    How do i go about merging both datasets? Can i query both simultaneously and will this allow me to arrive at a semi-accurate inventory count? I'm sure I could run a query everyday on both, merge the data in excel and use a few dozen formulas, but quite frankly, this would be messy and i need a solution that takes no more than 15 minutes to maintain daily, can be accessed for very specific information quickly and which, lesser computer advanced individuals would be able to use without much difficulty.

    Your ideas??

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As usual I'm sure there are a number of ways to accomplish this, but since both of your dbs are Access, why not do just that; merge the two! After backing up both dbs to a different folder (and I also make a copy on something this important on a cd as well) import all of the objects (tables, forms, reports, etc) from one db into the other. Then join the tables where necessary.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2006
    Location
    Calgary, AB
    Posts
    8
    'join the tables where necessary?

    this is from one db. this table originally had about 20-30 different fields,

    WarehouseNum ItemNum load_date Shipped
    101 31 6/26/2006 200
    101 31 6/27/2006 180
    101 338 6/26/2006 162
    101 338 6/27/2006 357

    this table is from another db. Notice how WarehouseNum is '9' instead of '1' and itemNum is '000'. This is just one of many impediments I face. As well, none of the table fields are the same, I changed them here to make it easier to work with.

    SupplierNum ItemNum WarehouseNum PlannedDelDate DeliveredQty
    869747 00031 901 6/26/2006 250
    869747 00031 901 6/27/2006 300
    869747 00338 901 6/26/2006 490
    869747 00338 901 6/27/2006 490


    The goal here is to query the two simultaneously (somehow), type in an ItemNum using a parameter and know for instance that on the 26th, (the day i started) we received 250 item31 and shipped 200 for a remaining balance of 50. The 50 is what I'm after.

    Advice? Best approach?
    Thanks

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by mi9
    The goal here is to query the two simultaneously (somehow), type in an ItemNum using a parameter and know for instance that on the 26th, (the day i started) we received 250 item31 and shipped 200 for a remaining balance of 50. The 50 is what I'm after.
    As Missinglinq says there are many ways to do this and it depends largely on how things are set up in your organisation, the options available and your long term strategy for this project (understanding you are having a major upgrade in two years). Are you running this over a network ? Are their multiple users ? How accurate does this need to be ? Do you have control to re-write the two databases already in existance of are you only allowed to view the data? What is your level of expertise? And many more questions..

    The method of calculating stock here is really a method of calculating a notional balance. This will work providing you an accurately account for all your stock as either sales or purchases. But I suspect the reality is stock gets lost, broken, writen-off etc. So you will need to handle a third set of transactions call "stock-adjustments" in order to balance your stock.

    The problem with calculating notional stock is that to find out the current balance you will need to run queries on all the transactions that ever existed for the product in question. If the product involves a lot of transactions then this could soon become unwieldy. So many stock management systems today typically have a stock table that is a record of the current stock. When a transaction takes place, it triggers the stock to be updated accordingly. This moves away from purist database theory because, as you point out, the stock can be calculated. However, it is more practical in its implementation (and tends to follow the philosophy of a double entry ledger book-keeping methodology). The risk in this solution is that if a transaction is entered but for whatever reason the trigger to update stock does take place, then the stock record is out of sync in system terms. So the solution has to be pretty robust and may have processes that cross-check that all is well.

    Back to your problem... The quick dirty answer is you use the DSum function which you can use in conjunction with your parameter entry (take a look at Dsum in Access Help). Dsum allows you to be quite specific about the criteria. So you could just create a formula on a report or form that comprised of two Dsum functions (one for orders, one for purchases). You can create your own database that contains linked tables to the ones already in existance then use Dsum to give you the theoretical stock balance. It's quick and easy to implement but might not be that accurate. You could add your own stock-adjustments table to your own database that would allow you to adust say when stock had gone missing then add another Dsum to incorporate this.

    As for your data, that again goes back to the question of what you plan to do long term. From what you say it's probably not worth a full redesign and cleaning of data. You may be better to add tables to your own database that convert the data e.g. 901 equates to 101. When I say convert, I don't actually mean change the data but allow you to introduce another column in a query saying "Standard Warehouse Number". If you knw the last two characters are the match then right("warehouse",2) will give you a comparable string. Similarly with your products you can write a function to remove the leading zeros (I'm sure I've seen many posts on this).

    Here's a plan:
    - Create your own database and add links to the required tables (2 tables ?)
    - For each table, create a select query that gives a "transformed" view of the data so that the product numbers and warehouse numbers are common (either using a lookup table or some text manipulation)
    - create a form or report and add a text box. Add the formula for calculating the stock using the Dsum function.

    hth
    Chris

  5. #5
    Join Date
    Jul 2006
    Location
    Calgary, AB
    Posts
    8
    Thanks for the help. Excellent reply. Hopefully i don't need too much help implementing your solution. I've created two linked tables and defined relationships between them. ItemNum and ItemNum for instance. Neither table has primary keys. Step two is complete more or less.

    'create a form or report and add a text box. Add the formula for calculating the stock using the Dsum function.'

    This is beyond my skillset. I've been looking at the MSDN and various tutorials on dsum but nothing is working. when I create a form or report do I need to add the related fields from both tables? Where do I enter a dsum formula? Is it a module.

    As well, this will be run over a network for 7 users. One of the databases stalled in development when my predecessor left. I've sort of resurrected it.

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    - Create a blank form (no need to add a record source)
    - add a text box
    - in the Control Source of the text box properties, add the Dsum formula

    The DSum statement will look something like this:
    =DSUM("[Shipped]","mySalesTable","load_Date <= #6/26/2006#")

    I've attached an example d/b. The d/b has two tables (sales & purchases) and two corresponding queries to standardise the data in the ways I explained previously. Actually, I converted the item to an integer in both cases.

    There is one form. The form has a text box to enter a required date, a text box to enter the required item and three text boxes showing the output. Try putting 6/26/2006 and 31 as the inputs.

    The Control Sources for the sales a purchases text boxes use the top two text boxes as parameters for the Dsum.

    The final box simply subtracts the purchase and sales text box.

    You can do a report in a similar way. Or you could create a report to list all items along with the total sales, total purchases and the balance for each item.

    hth
    Chris
    Attached Files Attached Files

  7. #7
    Join Date
    Jul 2006
    Location
    Calgary, AB
    Posts
    8
    Thanks a lot. I thought I could add a third text box myself as a parameter but have so far failed.

    I changed WHconvert so that WarehouseNum was carried over in the query rather than StdWHNum and tried to add a text box called WHchoice as a third parameter for the below. I screwed up somewhere though. By the way, what kind of courses do you take to excel at this? I suspect that MS Access courses alone wouldn't be sufficient.

    =DSum("[Shipped]","StdSales","Load_Date <= #" & [Forms]![form1]![DateChoice] & "# and StdItem = " & [Forms]![form1]![itemchoice] & "# and WarehouseNum = " & [Forms]![form1]![WHchoice])


    =DSum("[DeliveredQty]","stdPurchases","PlannedDelDate <= #" & [Forms]![form1]![DateChoice] & "# and StdItem = " & [Forms]![form1]![itemchoice] & "# and WarehouseNum = " & [Forms]![form1]![WHchoice])
    Attached Files Attached Files

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by mi9
    By the way, what kind of courses do you take to excel at this? I suspect that MS Access courses alone wouldn't be sufficient.
    I don't excel - more an average user. Some of the other bods in this forum are pretty clever though. I did courses in Relational Database Design and SQL. I haven't done any access courses so all of my Access knowledge has been learnt here in this forum and in a couple of good Access books. You gotta learn about relational database design if you're gonna get serious with Access (or any other dbms for that matter)

    Quote Originally Posted by mi9
    =DSum("[Shipped]","StdSales","Load_Date <= #" & [Forms]![form1]![DateChoice] & "# and StdItem = " & [Forms]![form1]![itemchoice] & "# and WarehouseNum = " & [Forms]![form1]![WHchoice])
    Good try. The & is use to join strings together. So my original criteria would have been inerpreted as:
    "Load_Date <= #6/26/2006# and StdItem = 31

    This is fine as the # tells access that 6/26/2006 is a date. Also, 31 is a number so StdItem = 31 is fine. But your criteria will be interpreted as:
    Load_Date <= #6/26/2006# and StdItem = 31 # and WarehouseNum = 901

    There are two problems. The hash you have included is not supposed to be there. And WarehouseNum is of data type text so you have to tell it that 901 is text by enclosing it in single quotes. The correct statement shoud be:
    Code:
    =DSum("[Shipped]","StdSales","Load_Date <= #" & [Forms]![form1]![DateChoice] & "# and StdItem = " & [Forms]![form1]![itemchoice] & " and WarehouseNum = '" & [Forms]![form1]![WHchoice] & "'")
    Which will be interpreted as
    Load_Date <= #6/26/2006# and StdItem = 31 and WarehouseNum = '901'


    Quote Originally Posted by mi9
    =DSum("[DeliveredQty]","stdPurchases","PlannedDelDate <= #" & [Forms]![form1]![DateChoice] & "# and StdItem = " & [Forms]![form1]![itemchoice] & "# and WarehouseNum = " & [Forms]![form1]![WHchoice])
    And...
    Code:
    =DSum("[DeliveredQty]","stdPurchases","PlannedDelDate <= #" & [Forms]![form1]![DateChoice] & "# and StdItem = " & [Forms]![form1]![itemchoice] & " and WarehouseNum = '" & [Forms]![form1]![WHchoice] & "'")
    hth
    Chris

  9. #9
    Join Date
    Jul 2006
    Location
    Calgary, AB
    Posts
    8
    Thanks for all your help.

Posting Permissions

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