Looking for Easiest Way to Create Report

[cowichandave]

We need to create a report from our accounting program which contains about
200 .db files. I have determined that the required information is contained
in only 3 of those files.
Out of those 3 files I need about 30 fields of data.

The end result is that those 30 fields of data have to be placed into an
existing excel spreadsheet in specific columns which has 97 total columns.

For example one db file contains data which has to be placed into column c
of the excel spreadsheet. another db file has data which has to be placed
into column z of that same spreadsheet and so on

The spreadsheet has the first 4 lines predefined so data has to be entered
starting on line 5. We cannot alter the spreadsheet because it is submitted
to another party.

I thought using Access to link the fields and extract the information would
be easiest and then to export it to an excel predefined format.

Any suggestions?

 

[KARL DEWEY]

so data has to be entered starting on line 5.
Access does not use 'lines' but records that are related. If your data can
not be related record to record then you have a bunch of manual work to do.
Why so many? Why are they not consolidated and have field(s) that
distinguishes the records into to groups?

 

[cowichandave]

The accounting program is a commercial one, so why so many db files is
beyond me but it works great and that is what we need to extract a report
from.

I looked at the 3 db files I require data from and yes they can be related.

So if we use Access to extract that data, how difficult will it be to export
each field into a specfic column in excel

 

[Philip Herlihy]

Well, with 200 *.db files this isn't something you can give a definitive
answer to here. Here's how I'd approach it (off the top of my head)

Create a new database file (yes, honest!) and link the relevant tables from
the three relevant database files.

Create a query to extract the figures you need. (Well, you didn't give us
much to go on!). You may perhaps need to link the destination spreadsheet
into Access so that you can join/match other data in that spreadsheet.

I understand that the data from Access needs to go in specific columns but
you haven't said whether the data needs to go in specific rows - ie: whether
the Access query needs to be related to data already in the spreadsheet. If
it does, then you do need to link to the spreadsheet from Access and craft
your query according to the relationships which exist in the data. Then you
should be able to reproduce the data portion of the worksheet (ie below the
four header rows) including the "missing" columns. If your query becomes a
make-table query you'll end up with a table in Access (re-created at each
run of the query) which you can "connect" to from Excel, which would make it
look like another worksheet. You can fiddle around in Excel to get your
four header rows on the top (that's beneath the dignity of Access).
Alternatively, you may be able to reproduce those four header rows as the
header of a report, which means the finished product, saved/exported in an
appropriate format, could be opened directly by your third party in Excel
(shudder), as Excel cells don't have data types.

The big question is: what's in the other 67 columns in that spreadsheet?

It does sound to me that the tail's wagging the dog here: you're distorting
your data management to comply with an ad-hoc structure imposed by an
arbitrary spreadsheet. In Access development, the data model (the table
structures and relationships) have absolute primacy - if you get that right
at the outset (which can be a struggle) everything else just falls into
place. It's a very different way of looking at things, and for this type of
work, a very much more powerful and robust one. If you could loosen your
third-party's grip on that spreadsheet he or she would probably find
better-structured output data easier to process.

With an application so large in scale, it's hard to give more than
generalities. I for one wouldn't be in a position to work on specifics with
you on this. By the way, you should note that this is a volunteer-based
group, so anyone who pitches to you for paid work is breaking the
honour-code here.

Phil, London

 

[cowichandave]

Thanks for your input. Lets forget that there are 200 db files. I have
isolated the data I need from 3 of those 200 files. I see that it is possible
to link these files together and extract the data I need. Unfortunately the
spreadsheet is from a multinational corporation which they have supplied to
probably hundreds of other companies so we have to comply to play with them.
Every company will no doubt have different accounting programs and they have
to figure out how to do this themselves. I have db files.

I would assume that the multinational wants the data submitted in a specific
format so they can merge data from each one of us into a master file.

Just assume that the type of data we are submiting is for product warranty
purposes. It would contain name, address, phone number, product, serial
number etc type of data and this data haas to match the columns that they
have supplied on their spreadsheet. The other 67 columns are data that will
be filled in by the multinational.

Treat the spreadsheet as table data. Each row as a new customer.
We have to submit a spreadsheet weekly so it would contain new data each
week that is only the current weeks activity

I am looking for suggestions on accomplishing this. You suggested linking
their spreadsheet to the database and running a query to populate the
spreadsheet. I didn't know that was possible so I will do some research on
this angle.

Much appreciated.

 

[John W. Vinson]

Thanks for your input. Lets forget that there are 200 db files. I have
isolated the data I need from 3 of those 200 files. I see that it is possible
to link these files together and extract the data I need. Unfortunately the
spreadsheet is from a multinational corporation which they have supplied to
probably hundreds of other companies so we have to comply to play with them.
Every company will no doubt have different accounting programs and they have
to figure out how to do this themselves. I have db files.

I would assume that the multinational wants the data submitted in a specific
format so they can merge data from each one of us into a master file.

Just assume that the type of data we are submiting is for product warranty
purposes. It would contain name, address, phone number, product, serial
number etc type of data and this data haas to match the columns that they
have supplied on their spreadsheet. The other 67 columns are data that will
be filled in by the multinational.

Treat the spreadsheet as table data. Each row as a new customer.
We have to submit a spreadsheet weekly so it would contain new data each
week that is only the current weeks activity

I am looking for suggestions on accomplishing this. You suggested linking
their spreadsheet to the database and running a query to populate the
spreadsheet. I didn't know that was possible so I will do some research on
this angle.

Much appreciated.

You will also want to look into "Excel automation", which will indeed let you
migrate data from a recordset (based on a query of the three linked db files -
are these dBase format files???) into the spreadsheet. Do a Bing or Google
search for "Access Excel automation" for any number of messages about the
subject.

 

[cowichandave]

Good suggestion on the automation. Yes these are db files not dbf. Access
treats them like Paradox files.

 

[Philip Herlihy]

If the spreadsheet is blank (apart from the four header rows) before you run
your query then that makes life very much easier than it would have been if
you'd had to match values line (record) by line.

I'm looking for the simplest and most future-proof way of doing this, and
less work is better. The approach I'd investigate first is to create a
query which generates the values you want and also the blank fields. Then
export that to Excel. This could give you a worksheet which has everything
except the four header rows.

To generate the empty columns, select the null string ("") with a name of,
say, "blank23". In the query builder you'd write:
blank23:""
.... in the corresponding field positions. I tried this myself, and it seems
to work. This is my test query in SQL view:
SELECT Contact_tbl.Contact_DisplayName, "" AS blank1, "" AS blank2
FROM Contact_tbl;
(this just selects one field from one table and two empty columns.

Then you can export the whole thing to a new Excel worksheet in one
operation, using the TransferSpreadsheet macro.

As John (much respected here) has pointed out, if you go into VBA you can
have much more fine-grained control. If you look at the CreateObject
Function in Help you'll see an introduction to how you can do this, but the
downside is there are an awful lot of places where a coding error can stop
the whole process working correctly.

So, if you do go for the query and TransferSpreadsheet route, that leaves
you with the problem of levering in the four header rows. You can't write a
query (not even a Union query) which will prepend these within Access as
Access demands consistency of data types within fields (a strength,
actually!). One possibility would be to use Automation (go back to the
CreateObject Function) to generate these unchanging (?) header lines in the
newly-created Excel worksheet from within Access - the advantage is that you
could control everything going on from within Access, possibly from one
button-click. My guess is that the simplest solution will be in Excel, if
you're prepared to do the job in two stages: one which generates the
spreadsheet from Access, and another which requires you to move to Excel to
add the headers. Excel has a great facility which allows you to record a
macro - it generates VBA code to do what it monitored you doing manually.
Then you can adapt the generated code in Excel to fine-tune (add some
error-handling code unless you like guesswork down the line: see this,
especially the nine numbered lines: http://allenbrowne.com/ser-23a.html).
Excel VBA allows you to open other Excel files and read the contents, so you
may simply set up the headers in another file and use your macro to push
them onto the top of your sheet which was generated by Access.

There may well be a more elegant way, but it escapes me for now. Might be
worth asking in an Excel group too! Note that automation works both ways -
you can interact with an Access database using code in an Excel spreadsheet,
so a macro in Excel could run the query (perhaps it would need to be a
make-table query as you can't access the output of a query directly) and
build the finished structure that way.

HTH

Phil

 

[Philip Herlihy]

Had another look in Excel myself. I see you can set up a "Connection" at a
specific location (cell A of row 5?) to an Access database table (use
Make-Table or maybe Update query in Access). It's also worth looking at the
Consolidate facility in Excel (new to me!). By recording Excel macros and
editing them (always add error-handling!) you may be able to get all this
down to one button push in each application.

Best of luck. (Away for a few days now.)

Phil

 

[Philip Herlihy]

Don't bother with Consolidate!

Phil

Had another look in Excel myself. I see you can set up a "Connection" at
a specific location (cell A of row 5?) to an Access database table (use
Make-Table or maybe Update query in Access). It's also worth looking at
the Consolidate facility in Excel (new to me!). By recording Excel macros
and editing them (always add error-handling!) you may be able to get all
this down to one button push in each application.

Best of luck. (Away for a few days now.)

Phil