Sparsly Populate an Excel Spreadsheet from code

[fly_champ]

I am a mechanical engineer trying to dump off some of the information collected about direct labor for the accounting people. Since I am the only person here who can program.....

(Anything is subject to modification)

I have two tables, table 1(actually a query of a longer table), stores daily entered information for job cards

"date","name","hours","part","division"

table 2 stores constants, ie people who can only work under 1 division.
It has the same fields, with "date" and "part" remaining blank, and "hours" containing a numeric value

The plan was to add the tables together, and crosstab the results into an Excel Spreadsheet Template.

The problem, I can't get the data to fill in the layout(sparse) of the spreadsheet that the report must be formatted in.
I can export to excel using transferspreadsheet, but the fields are in the wrong order, and there is no formatting in the command.
I have tried using some workbook code, but there is little documentation, and my tries have failed.

The report is like a crosstab query with some sums on it. IE

|division1 | division2 | division3 |
name1 | x hours | | | sum_name1
name2 | | | y hours | sum_name2
name3 | | z hours | | sum_name3
---------------------------------------
| sumdiv1| sumdiv2 | sumdiv3 |

Thanks in advance for your time.

 

[Norie]

Have you looked at creating a PivotTable in Excel from your raw data, missing out the crosstab?


It sort of does what a crosstab does.

 

[fly_champ]

thank you for your time norie

I have experimented with a pivot table, but like a crosstab report, it needs all the data in 1 concatenated table. So maybe my issue here is needing some way to merge the tables together, or a way to compare the data on row at a time, and output the results into excel.

The pivot table does seem like a useful way to get data between the programs,
but I didn't see any way to call it from code, which is important,
since the people who need this information are not very computer literate.
I need to have a single button that outputs an excel spreadsheet.

 

[Norie]

You could create code in Excel that automatically creates a pivot table from a button.

I'm still not clear on how you're data is structured and the tables linked.

 

[fly_champ]

My data is structured simply like my example in the first post.
I have managed to get the results that I need through a hefty combination of queries, but I am looking for a better way.

The information I collect is :

*name*,*date*,*hours*,*part*
name1,  date1  , time1   , part1
name1,  date1  , time2   , part2
name1,  date1  , time3   , part1
etc

the parts table has(among other fields)

*partname*,*partdivision*,*customer*,etc
     part1   ,   oem       ,  nat8
     part2   ,   oem       ,  mor4
     part3   ,   sky       ,  fox1

the information I am trying to export is:

. 'date' division1,division2,division3, sum
name1,  time1   ,  time2   ,           , timesum1
name2,  time3   ,          ,           , timesum2
name3,          , time4    , time5     , timesum3
----------------------------------------------------
          sumdiv1, sumdiv2, sumdiv3

This is how it works now,
I query the list of daily information to link the part to the division.
I have a list of people who always work in the same division.
I compare the two lists to make sure I have unique names.
I make a table from the first query.
I append that table with the reaminder from the unique name query.
I crosstab the end result (appended table).
I dump the crosstab into excel via "transferspreadsheet"

This seems very sloppy, and though it does work, I get 50 messages warning me that I am about to change information in a table

I am not a power user of excel and access, but I know how to code to an extent.
I just think I am trying to hard when a more elegant solution is better and more adaptable to changes in the program.
Right now I'd be lucky to remember which query name does what.
I also am no expert at designing queries.