Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    daverj is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Finger Lakes
    Posts
    10
    Quote Originally Posted by ssanfu View Post
    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.
    Thanks for pointing me in the direction of using a recordset. I use ADODB recordsets in a number of places in my front end for temporary tables (the back end is SQL Server), so I created an ADODB connection and recordset pointing to the spreadsheet and am now importing the spreadsheet into a recordset. That is working well.



    Quote Originally Posted by Ajax View Post
    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)
    It's a good idea to concatenate the Section, Drawer, and Available into one "Location" field as part of the Query. Thanks.

    But I don't understand how a crosstab would work in this situation. The parttype and quantity are specific to a given intpartnum. And the db might have multiple records for each intpartnum, which represent multiple locations for that intpartnum. But those multiple locations aren't identified as Location1, Location2, etc... in the db. There just may be multiple records for a given intpartnum. Defining them as Location1, 2, etc... is done after the fact just based on how many locations a given intpartnum has, with the prior knowledge that no part is likely to have more than 4 records.

    A crosstab is normally done where one of the original fields has something in common with multiple entries in a different field, and one of those fields then becomes a set of rows instead of columns. That isn't the case here. None of the locations will match any other location.

    Plus, a requirement of all of this is that the output spreadsheet has the same number of rows as the input spreadsheet, and contains all of the columns of the original (there are additional columns that I didn't show in the simplified examples above). The output spreadsheet is basically a copy of the input one, with some additional columns.

  2. #17
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Defining them as Location1, 2, etc... is done after the fact just based on how many locations a given intpartnum has, with the prior knowledge that no part is likely to have more than 4 records.
    you would have a subquery to calculate a row number by partnumber whatever (i.e. getting 1,2,3 etc) which you concatenate to 'Location' to get your column headings. However I would expect it to be slow due to volume of data and most likely lack of indexing.

    Anyway,I'm out of ideas. Sounds like your data may be too denormalised to be manipulated in a database in the way you want.

    Good luck with your project

  3. #18
    daverj is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Finger Lakes
    Posts
    10
    I'm not really "manipulating" the data from the spreadsheet. Just filling in the blanks. The info from the spreadsheet is used to get additional info about itself from the database. At this point I'm almost where I want to be. I just have to figure out how to join the recordset with the tables in the database to extract what I need from the database. The rest is pretty much done now.

    Thanks.

  4. #19
    daverj is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Finger Lakes
    Posts
    10
    I got all of this working, and wanted to thank those who helped. It was more complex than I originally expected, but I also ended up adding a lot more features than I had planned. So it now will save me a lot of time.

    Originally I was exporting a spreadsheet from my CAD design system that contained a list of all parts used in a design, with every part listed individually. I would then massage that in Excel to combine the duplicates and create a "Quantity" field. Plus I would do a lot of sorting to get everything in a useful order. I would then hand that to my assistant who would look up each part in our inventory database to find the physical locations of all the parts.

    What I ended up doing was to import the raw spreadsheet into an ADO recordset, combining the duplicates and creating the Quantity field in the process. Then I used the part numbers to query the database and find all the physical locations. Initially I tried to then insert that info back into the original recordset, but wasn't able to because it was an aggregate recordset. So I ended up creating a third recordset where I copied all the info from the first one and then combined in all the location info. I then exported that to an Excell spreadsheet, and formatted the cells, adjusted column widths, used a couple of helper columns to sort everything (part values are complex number/letter combinations so didn't sort well as-is), then finally added a top row with the name of the design extracted from the filename, and then saved it all back to the disk.

    This will end up saving us a lot of time. It runs fairly quickly, and just requires me to pick an input file and define an output file name. The rest is automatic.

    So again, thanks for the help.

Page 2 of 2 FirstFirst 12
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