Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    daverj is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Finger Lakes
    Posts
    10

    query database from spreadsheet

    This is kind of complex, so please bear with me...

    I have an inventory database system that I created in Access 2013. It has tables for manufacturer parts, distributor parts, for the number of parts in inventory, and where those parts physically are stored. Those are all linked to a table of "internal part numbers". So each part in the main table of internal part numbers might have one or more manufacturer parts linked to it. Those might have one or more distributor part numbers linked to them. The internal parts also have one or more entries in a quantities table showing how many parts are in stock and in what locations they exist. The same part can exist in several locations, so it can have several entries in the quantities table.

    Now at the same time, I create spreadsheets (in Excel) from my circuit board CAD system that list the parts on a given circuit board, which includes a column of the internal part numbers. I then hand that spreadsheet to my assistant who gathers the parts to assemble a prototype circuit board. Right now he has to manually look up each internal part number in the inventory database to find where the physical location is for each of those internal part numbers.



    What I would like to do is to extract that internal part number column from the spreadsheet and some how pass it through a bulk query in the database and get back a new spreadsheet (or .csv file) report showing the location and quantity for the current stock of each of those parts based on their internal part number. The spreadsheet might have 50 or 100 rows of parts in it that all have to be looked up. The spreadsheet also has a number of other columns in it that I need to retain. So I am thinking that rather than hand the entire spreadsheet to so type of query, that I would first extract that one column of internal part numbers and some how pass that to a query. Then the query would return maybe 4 columns of results (a single part might be in as many as 4 locations) and I would paste those 4 columns back into the original spreadsheet. That's just a thought.

    Any suggestions for how this could be accomplished?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    probably easier to do in access rather than excel.

    in access link to your excel worksheet then just use a query to return the data you want

  3. #3
    daverj is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Finger Lakes
    Posts
    10
    I was assuming the query would be done in Access. But how do I do a query that pulls a column from an external Excel spreadsheet and runs that query using data in a column from that spreadsheet? I have only done queries of tables that are within my Access database. This would require popping up a file finder so I can select a spreadsheet, then either already knowing the specific column, or somehow selecting that, and then running a query using that column as part of the "Where" statement within my database query.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    But how do I do a query that pulls a column from an external Excel spreadsheet and runs that query using data in a column from that spreadsheet?
    as I suggested, link to the excel spreadsheet. On the ribbon, select external data>excel and follow the prompts. This will create a linked table which you can reference in queries in the same way as you do with other tables.

    This would require popping up a file finder
    yes - but you can relink to another file rather than creating a new linked table and query every time

    either already knowing the specific column, or somehow selecting that
    providing your excel spreadsheets have the same structure and column headings it won't be a problem - you refer to the column heading in your query

    you haven't provided any detailed specifics so I can't provide a detailed answer. but assuming your database has a table called 'parts' which has the part numbers in it in a field called partNo and your spreadsheet has a column called partNum. And assuming your worksheet is called sheet1 then a query along these lines would produce the 'link' for the required parts to your location table

    SELECT *
    FROM parts INNER JOIN sheet1 ON parts.partno=sheet1.partnum

  5. #5
    daverj is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Finger Lakes
    Posts
    10
    Hi, I'm finally getting back to this. I tried a few things but have hit some stumbling blocks that I'm not sure how to get past. Let me give a few more details:

    I have an Access 2013 project (.accdb) with a number of tables, and lots of forms and reports. The data lives on an SQL Server 2012 (on the same computer). For what I am trying to do now, three tables are involved. The "PartsQuantity", "Drawers", and "Sections" tables. The PartsQuantity table contains columns for "IntPartNum", "SectionNum", "DrawerNum", and "QuantityParts" (as well as a column containing a unique key index number). The IntPartNum is not unique. There might be 0, 1, 2, or more rows each with a given IntPartNum, representing different locations (Section and Drawer) where that part might be found. The Sections table just contains a Section Number (unique key) and a SectionName column. Likewise the Drawers table contains a DrawerNum column (unique key) and a DrawerName column. The query will pull the SectionName and DrawerName from their respective tables, where they match the corresponding SectionNum and DrawerNum in the PartsQuantity table.

    The idea is to add a button to the main form of the Access project. When that button is pressed a file browser pops up asking which spreadsheet to open. I then browse to one of several hundred spreadsheets and select it. Access would then open that spreadsheet, find the IntPartNum column in it, and for each row of the spreadsheet it would run a query of the Access db and return the Section & Drawer Names for where that part can be found, plus the quantity. Ideally placing the results in a new column of that same spreadsheet.

    A couple of stumbling blocks:

    1 - I need to use a file browser pop-up to select a spreadsheet and then the work will take place on that specific spreadsheet. I do my editing and modifications to the project using Access, but for daily use of the project it is run via the Access Runtime. So no editing is possible in normal daily use. (I have a couple of assistants who use the program, but I don't want them to accidentally modify the project files, so it's run via Runtime and not via the full version of Access, unless I have to edit something myself)

    2 - The IntPartNum in the spreadsheet is a text field with a 1 or 2 digit number, a dash, and 4 more digits. The same field in the Access db is a 5 or 6 digit number (no dash). So the spreadsheet might contain 32-1234 while the Access db contains 321234. So I somehow have to remove the dash within the query, or if that isn't possible, then I would have to create a temporary table copying the column from the spreadsheet, then filter that temp table to remove the dashes, then run the query using that temp table.

    3 - There might be 0, 1, 2, or more rows returned from the query for any given IntPartNum. The spreadsheet that I want this result to go back into only has one row per IntPartNum. So I need to combine multiple rows of result from a given query into a single row. The actual format of that is unclear. If I only had a single result for each part number I could see placing the results into 3 columns (Section, Drawer, Quantity). But with multiple rows of results that have to be returned as a single row, it would make more sense to either return each row as a cell next to each other (I think 4 locations is the maximum for any part). So if one row is returned, that first cell would contain the three fields concatenated as "Section, Drawer, Quantity". If more rows are returned for that part their fields would each be concatenated the same way and placed in consecutive cells in that same row of the original spreadsheet. Alternately they could all be concatenated into one big cell, like "Section1, Drawer1, Quantity1 - Section2, Drawer2, Quantity2 - Section3, ....etc.. Whatever is simplest is fine.

    So, you can see this is not a simple thing. Plus I am not an Access Expert. I've been stumbling along with Access for maybe 15 years, slowly building this project into what it is. But I know just enough to be dangerous. Though I've also done some SQL in other databases, so the core of the query needed here is something I do understand, except for the issues mentioned above (stripping the dash out of the source field, and combining several rows of results into one).

    Any suggestions would be appreciated. (the more detailed, the better)

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1. use filedialog - google to find plenty of examples

    2. use the replace function to replace the '-' with a zls ("") - in the sql example I provided before
    SELECT *
    FROM parts INNER JOIN sheet1 ON parts.partno=replace(sheet1.partnum,"-","")

    3. use a separate table, not additional columns - a reality of using a database.

    your big issue is to get away from excel thinking. Access is the opposite.

  7. #7
    daverj is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Finger Lakes
    Posts
    10
    Quote Originally Posted by Ajax View Post
    1. use filedialog - google to find plenty of examples
    Getting the filename is the easy part. It's what to do with it after that. This is all going to have to happen in the OnClick event for my button, so written in VBA. After some searching I found mention of two ways to get to the spreadsheet. DoCmd.TransferSpreadsheet acLink,.... Or using ADO and a recordset. I don't know if there's advantages to one over the other, or if either one can export the results back into the original spreadsheet (or into a new copy with the added results) And I don't know yet how to connect to the spreadsheet with either of those methods.

    Quote Originally Posted by Ajax View Post
    2. use the replace function to replace the '-' with a zls ("") - in the sql example I provided before
    SELECT *
    FROM parts INNER JOIN sheet1 ON parts.partno=replace(sheet1.partnum,"-","")
    Perfect. Thanks.

    Quote Originally Posted by Ajax View Post
    3. use a separate table, not additional columns - a reality of using a database.
    Not sure what you are suggesting here. No data is being added to the database. The goal is to add data to the spreadsheet. The number of rows in the spreadsheet won't change. One or more new columns will be added for the new data. The issue is that for each row of the original spreadsheet, the db query might return a temp table with 0~4 rows by 3 columns of result. That must all end up added back into that original single row of the spreadsheet.

    If there was always only one row of result for the query, then I would simply add those three columns into the original spreadsheet as 3 new columns. But since that's not the case I have to figure out how to fit the multi-row result into the original single row. For example, select the result with the highest quantity and only insert that row from the result table into the spreadsheet. Or combine all of the columns and rows into a single cell. (that would make that new column be very large at times) Another option would be to create 12 new columns. The query results then are converted from a 4x3 grid into a 1x12 piece of a row. (or the same thing, but 4 new columns where each new cell holds the concatenated results from one of the rows of the results table)

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Not sure what you are suggesting here. No data is being added to the database.
    You need to read up on normalisation.

    DoCmd.TransferSpreadsheet acLink,.... Or using ADO and a recordsetuse
    transferspreadsheet. Better to import the data to a temporary table then use append queries to copy the data to the 'real' tables. To avoid bloat, put the temporary table in a temporary db. Or you can link instead, but you have less control over data typing (Excel can be a problem in this regard). Take the opportunity at this stage to normalise the data (Excel is rarely normalised).

    For exporting, be aware you cannot 'edit' an existing excel file, all you can do is export to a new one.

    If your only reason for doing this is to combine data from multiple excel files into one excel file, you will probably be better doing it all with excel macros and don't go via a database. You will hit too many issues because excel and databases are two different things.

  9. #9
    daverj is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Finger Lakes
    Posts
    10
    Quote Originally Posted by Ajax View Post
    Not sure what you are suggesting here. No data is being added to the database.
    You need to read up on normalisation.
    I know what normalization is, and it is irrelevant here. Everything in the spreadsheet is text except the part number and the quantity. Both would be useless and meaningless if they were normalized.

    Quote Originally Posted by Ajax View Post
    For exporting, be aware you cannot 'edit' an existing excel file, all you can do is export to a new one.
    Thanks. That's what I needed to know

    Quote Originally Posted by Ajax View Post
    If your only reason for doing this is to combine data from multiple excel files into one excel file, you will probably be better doing it all with excel macros and don't go via a database. You will hit too many issues because excel and databases are two different things.
    I thought I was clear about what I'm doing, but let me give an overview. I have a database system where I keep track of all of the parts I use in all my products (electronic parts, hardware, circuit boards, etc...). When I create a new design I generate a spreadsheet from the CAD system. It has no way to link to the db, it only has the ability to create a spreadsheet listing the parts used in the design. I then hand that spreadsheet over to an assistant who gathers up all the parts to build that design, or orders parts we are missing. He needs to know where the parts are located, and how many are there. The spreadsheet contains a list of the parts used in the design, but the CAD system doesn't know where they are located or how many we have. The database knows that. So I want the database to read the spreadsheet and create a new spreadsheet that has the location and quantity info added to it.

    One of the issues is that each part might exist in multiple locations, with different quantities of that part in each location. So there are basically two options. Either I do a large query that finds all the locations for all the parts in the spreadsheet, and put that into a temp table. And then run a loop with multiple queries that extract the records from that relating to a single part number, and then combine those records into something that will fit into a single row of the spreadsheet (one row for each part, no matter how many locations that part might be in). Alternately I can do a loop with a single query per part, getting a temp table back each time that might contain 0 through 4 records relating to an individual part. And then combine those records into something that will fit on the one row for that part number.

    Lets say a design has 50 parts. Method 1 above would start with one query of the PartsQuantity table (which has maybe 10,000 records). That results in a temp table with maybe 125 records of parts locations. I then do a loop of 50 queries (one per part), each resulting in 0~4 records that I then combine into a single record to insert into the spreadsheet. Alternately I simply do a single loop of 50 queries of the larger PartsQuantity table and then process each of those into the new record for each line of the spreadsheet. The difference is one query of the large table then 50 queries of the smaller results table vs 50 larger queries directly. Time-wise it probably makes no real difference, so the second one is more CPU processing but simpler code.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Still sounds like a normalisation issue to me. Regret I cannot fully visualise your requirement. I don't understand why you need 50 queries (I understand there are 50 parts which is where the 50 comes from) when you could do it with one query joining the tables.

    With regards the four parts, perhaps the concatrelated function might be a solution http://allenbrowne.com/func-concat.html

    good luck with your project

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Ajax View Post
    For exporting, be aware you cannot 'edit' an existing excel file, all you can do is export to a new one.
    Ajax, not sure why you say this.

    I use a query to generate a report (actually several) I want exported to Excel. Each time and each report is a new workbook.
    Then I have code (using DAO) that edits the spreadsheet. I add rows, change font, and add formulas to get column totals, then saves the workbook.

    Maybe I am misunderstanding......

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Ajax, not sure why you say this.
    Sorry, I was talking in relation to linked tables and using transferspreadsheet. Not tried DAO but do use the excel library to modify the spreadsheet (creating pivots/graphs, setting colors/font, etc) so could be used for populating cells but suspect quite slow. OP might find it useful for a DAO example - suggest you provide an example...

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is what I came up with after days of trial and error. Created/recorded the code in Excel to see what formatting looked like in Excel, then adapted the Excel code to Access. Ken Snell's site was a BIG help.
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm


    Saves the data: (EQ_ = Export query)
    Code:
    strSaveFileName = sPath & "\Statement Monthly Summary " & Me.cboMonth & " " & Me.cboYear & " Hours.xlsx"
    DoCmd.OutputTo acOutputQuery, "EQ_StatementMonthlySummary", acFormatXLSX, strSaveFileName, False

    Then calls a sub to do the editing/formatting:
    Code:
    Call EditVendorWkSht(strSaveFileName, "Statement Monthly Summary", Me.cboMonth & " " & Me.cboYear & " Hours")

    The code to edit/format the worksheet:
    Code:
    Sub EditVendorWkSht(pWkshtPathName As String, pVendor As String, pMthYr As String)
    
        Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
        Dim sRow As Integer, eRow As Integer, RowDiff As Integer
    
        Dim blnEXCEL As Boolean
    
        blnEXCEL = False
    
        ' Establish an EXCEL application object
        On Error Resume Next
        Set xlx = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
            Set xlx = CreateObject("Excel.Application")
            blnEXCEL = True
        End If
        Err.Clear
        On Error GoTo 0
    
        ' Change True to False if you do not want the workbook to be
        ' visible when the code is running
        '    xlx.Visible = True
        xlx.Visible = False
    
        ' Replace C:\Filename.xls with the actual path and filename
        ' of the EXCEL file into which you will write the data
        Set xlw = xlx.Workbooks.Open(pWkshtPathName)
    
        'get worksheet name
    
        ' Replace WorksheetName with the actual name of the worksheet
        ' in the EXCEL file
        ' (note that the worksheet must already be in the EXCEL file)
        Set xls = xlw.Worksheets(1)
    
        ' Replace A1 with the cell reference into which the first data value
        ' is to be written
        Set xlc = xls.Range("A1")   ' this is the first cell into which data go
    
        'add two rows at the top
        xls.Rows("1").EntireRow.Insert
        xls.Rows("1").EntireRow.Insert
        xls.Range("A1").Select
        xls.Range("A1").FormulaR1C1 = pVendor
        xls.Range("A2").Select
        xls.Range("A2").FormulaR1C1 = pMthYr
    
        If pVendor = "Statement Monthly Details" Then
            With xls
                .Range("F4").Select
                sRow = ActiveCell.Row
    
                ActiveWindow.FreezePanes = True
                .Range("H4").Select
    
                'find the last row and move down 1 row
                Selection.End(xlDown).Select
                Selection.Offset(1, 0).Select
                eRow = ActiveCell.Row
                RowDiff = eRow - sRow
    
                'select columns H through P
                .Range(Cells(eRow, 8), Cells(eRow, 25)).Select
                'set borders
                Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                Selection.Borders(xlEdgeLeft).LineStyle = xlNone
                With Selection.Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With Selection.Borders(xlEdgeBottom)
                    .LineStyle = xlDouble
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThick
                End With
    
                Selection.Borders(xlEdgeRight).LineStyle = xlNone
                Selection.Borders(xlInsideVertical).LineStyle = xlNone
                Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
                'add formulas
                Selection.FormulaR1C1 = "=SUM(R[-" & RowDiff & "]C:R[-1]C)"
    
                .Range("A4").Select
                .Range("A2").Select
            End With
        End If
    
        ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
        Set xlc = Nothing
        Set xls = Nothing
        xlw.Close True   ' close the EXCEL file and save the new data
        DoEvents
        Set xlw = Nothing
        If blnEXCEL = True Then
            xlx.Quit
        End If
        Set xlx = Nothing
    End Sub

  14. #14
    daverj is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Finger Lakes
    Posts
    10
    Quote Originally Posted by Ajax View Post
    Still sounds like a normalisation issue to me. Regret I cannot fully visualise your requirement. I don't understand why you need 50 queries (I understand there are 50 parts which is where the 50 comes from) when you could do it with one query joining the tables.
    In my example with a 50 row spreadsheet I can do one db query, which will return maybe 125 records. I then have to scan through that result table 50 times to find the records for each part and concatenate the appropriate records back down to a total of 50 records so they can be inserted into the original 50 rows of the spreadsheet. So I either run that one query and then scan through that table 50 times to find all the records matching each of the 50 parts, or I simply do 50 queries that each give me the exact set of records for that part number.

    Quote Originally Posted by Ajax View Post
    With regards the four parts, perhaps the concatrelated function might be a solution http://allenbrowne.com/func-concat.html
    The way that function works, it would have to be run 50 times. Once for each IntPartNum. As it's written it only combines the results from one field. It would have to be expanded to combine the values in the 3 fields that I need to concat. But the bottom line is that it does a query for each "CompanyID" (in my case it would be once for each IntPartNum). So it still runs 50 queries to do them all. So it's exactly what I was describing as Method #2 a couple of posts above.

    Maybe these will help:


    or this.......

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would go for the second option linking your input file to your large query in a crosstab query to produce your output spreadsheet - concatenating section, drawer and available into one field as the value, parttype, quantity and partnum as row headers and location as column header (which is not showing in your large query)

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help updating database from spreadsheet
    By KelvClark in forum Queries
    Replies: 3
    Last Post: 10-20-2015, 12:18 PM
  2. Replies: 3
    Last Post: 06-02-2015, 06:48 PM
  3. Replies: 1
    Last Post: 05-28-2014, 10:59 PM
  4. Replies: 1
    Last Post: 03-05-2012, 04:21 PM
  5. import an excell spreadsheet in an access database
    By lmichaud in forum Import/Export Data
    Replies: 0
    Last Post: 11-03-2006, 08:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums