Looking for Easiest Way to Create Report

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?
-- 
CowichanDave
0
Utf
12/3/2009 6:07:01 PM
access.gettingstarted 618 articles. 1 followers. Follow

9 Replies
809 Views

Similar Articles

[PageSpeed] 26

>>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.

>>our accounting program which contains about 200 .db files.
Why so many?  Why are they not consolidated and have field(s) that 
distinguishes the records into to groups?

-- 
Build a little, test a little.


"cowichandave" wrote:

> 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?
> -- 
> CowichanDave
0
Utf
12/3/2009 6:59:01 PM
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
-- 
CowichanDave


"KARL DEWEY" wrote:

> >>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.
> 
> >>our accounting program which contains about 200 .db files.
> Why so many?  Why are they not consolidated and have field(s) that 
> distinguishes the records into to groups?
> 
> -- 
> Build a little, test a little.
> 
> 
> "cowichandave" wrote:
> 
> > 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?
> > -- 
> > CowichanDave
0
Utf
12/3/2009 7:09:01 PM
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" <cowichandave@discussions.microsoft.com> wrote in message 
news:CE283F85-0567-4F54-97C4-0753014B046E@microsoft.com...
> 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
> -- 
> CowichanDave
>
>
> "KARL DEWEY" wrote:
>
>> >>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.
>>
>> >>our accounting program which contains about 200 .db files.
>> Why so many?  Why are they not consolidated and have field(s) that
>> distinguishes the records into to groups?
>>
>> -- 
>> Build a little, test a little.
>>
>>
>> "cowichandave" wrote:
>>
>> > 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?
>> > -- 
>> > CowichanDave 

0
Philip
12/3/2009 9:06:55 PM
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.


-- 
CowichanDave


"Philip Herlihy" wrote:

> 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" <cowichandave@discussions.microsoft.com> wrote in message 
> news:CE283F85-0567-4F54-97C4-0753014B046E@microsoft.com...
> > 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
> > -- 
> > CowichanDave
> >
> >
> > "KARL DEWEY" wrote:
> >
> >> >>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.
> >>
> >> >>our accounting program which contains about 200 .db files.
> >> Why so many?  Why are they not consolidated and have field(s) that
> >> distinguishes the records into to groups?
> >>
> >> -- 
> >> Build a little, test a little.
> >>
> >>
> >> "cowichandave" wrote:
> >>
> >> > 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?
> >> > -- 
> >> > CowichanDave 
> 
> .
> 
0
Utf
12/4/2009 4:43:20 AM
On Thu, 3 Dec 2009 20:43:20 -0800, cowichandave
<cowichandave@discussions.microsoft.com> wrote:

>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.
-- 

             John W. Vinson [MVP]
0
John
12/4/2009 5:32:35 AM
Good suggestion on the automation. Yes these are db files not dbf. Access 
treats them like Paradox files.
-- 
CowichanDave


"John W. Vinson" wrote:

> On Thu, 3 Dec 2009 20:43:20 -0800, cowichandave
> <cowichandave@discussions.microsoft.com> wrote:
> 
> >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.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
12/4/2009 7:17:01 AM
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



"cowichandave" <cowichandave@discussions.microsoft.com> wrote in message 
news:AD906BDB-D72C-48E9-B861-8F46EE4E4423@microsoft.com...
> 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.
>
>
> -- 
> CowichanDave
>
>
> "Philip Herlihy" wrote:
>
>> 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" <cowichandave@discussions.microsoft.com> wrote in message
>> news:CE283F85-0567-4F54-97C4-0753014B046E@microsoft.com...
>> > 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
>> > -- 
>> > CowichanDave
>> >
>> >
>> > "KARL DEWEY" wrote:
>> >
>> >> >>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.
>> >>
>> >> >>our accounting program which contains about 200 .db files.
>> >> Why so many?  Why are they not consolidated and have field(s) that
>> >> distinguishes the records into to groups?
>> >>
>> >> -- 
>> >> Build a little, test a little.
>> >>
>> >>
>> >> "cowichandave" wrote:
>> >>
>> >> > 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?
>> >> > -- 
>> >> > CowichanDave
>>
>> .
>> 
0
Philip
12/4/2009 2:08:59 PM
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" <thiswillbounceback@you.com> wrote in message 
news:OTAcTtOdKHA.4112@TK2MSFTNGP06.phx.gbl...
> 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
>
>
>
> "cowichandave" <cowichandave@discussions.microsoft.com> wrote in message 
> news:AD906BDB-D72C-48E9-B861-8F46EE4E4423@microsoft.com...
>> 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.
>>
>>
>> -- 
>> CowichanDave
>>
>>
>> "Philip Herlihy" wrote:
>>
>>> 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" <cowichandave@discussions.microsoft.com> wrote in message
>>> news:CE283F85-0567-4F54-97C4-0753014B046E@microsoft.com...
>>> > 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
>>> > -- 
>>> > CowichanDave
>>> >
>>> >
>>> > "KARL DEWEY" wrote:
>>> >
>>> >> >>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.
>>> >>
>>> >> >>our accounting program which contains about 200 .db files.
>>> >> Why so many?  Why are they not consolidated and have field(s) that
>>> >> distinguishes the records into to groups?
>>> >>
>>> >> -- 
>>> >> Build a little, test a little.
>>> >>
>>> >>
>>> >> "cowichandave" wrote:
>>> >>
>>> >> > 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?
>>> >> > -- 
>>> >> > CowichanDave
>>>
>>> .
>>> 
0
Philip
12/4/2009 2:24:56 PM
Don't bother with Consolidate!

Phil

"Philip Herlihy" <thiswillbounceback@you.com> wrote in message 
news:#4D5N2OdKHA.5796@TK2MSFTNGP06.phx.gbl...
> 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" <thiswillbounceback@you.com> wrote in message 
> news:OTAcTtOdKHA.4112@TK2MSFTNGP06.phx.gbl...
>> 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
>>
>>
>>
>> "cowichandave" <cowichandave@discussions.microsoft.com> wrote in message 
>> news:AD906BDB-D72C-48E9-B861-8F46EE4E4423@microsoft.com...
>>> 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.
>>>
>>>
>>> -- 
>>> CowichanDave
>>>
>>>
>>> "Philip Herlihy" wrote:
>>>
>>>> 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" <cowichandave@discussions.microsoft.com> wrote in 
>>>> message
>>>> news:CE283F85-0567-4F54-97C4-0753014B046E@microsoft.com...
>>>> > 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
>>>> > -- 
>>>> > CowichanDave
>>>> >
>>>> >
>>>> > "KARL DEWEY" wrote:
>>>> >
>>>> >> >>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.
>>>> >>
>>>> >> >>our accounting program which contains about 200 .db files.
>>>> >> Why so many?  Why are they not consolidated and have field(s) that
>>>> >> distinguishes the records into to groups?
>>>> >>
>>>> >> -- 
>>>> >> Build a little, test a little.
>>>> >>
>>>> >>
>>>> >> "cowichandave" wrote:
>>>> >>
>>>> >> > 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?
>>>> >> > -- 
>>>> >> > CowichanDave
>>>>
>>>> .
>>>> 
0
Philip
12/4/2009 2:26:27 PM
Reply:

Similar Artilces:

Change datasource in report before render
On my reportserver (on SQL Server 2008) I have one folder with report templates. If I open one template, I can change datasource. I want use this template in Excel or Word AddIn / Plugin with VSTO. In this case I want choose a report and then choose a datasource. I do following: Warning[] warnings; wsReportExecution2005.LoadReport2(reportName, null); buffer = wsReportExecution2005.Render2( reportAs, string.Empty, PageCountMode.Actual, out extension, out mimeType, out encodin...

missing FRx reports #2
Hello: A client of ours on FRx 6.7 service pack 6 and GP 8.0 says that she created three FRx reports and now they're gone. They have over 50 companies in FRx and about 15 different specification sets. I looked at the building blocks in the Specification Set window by clicking "Export". I do not see the three reports that my client says is missing. I deleted the .g32 files and compacted the system database and spec set data base of the company whose reports are missing. No success! Any ideas? Thanks! childofthe1980s Which components are missing? Do they have backu...

Report showing ID numbers only
I have tables set up, linked properly, forms, etc. Since I did forms and subforms, I had to create separate tables and link them, so I used a primary key ID in the tables. So far my database shows it is working - when information is entered into the form, it shows up in the correct tables and all that, but since I used all these primary keys, the tables just have a bunch of the numbers that pertain to what was input rather than actual words. I created a query with the fields that I want shown in the report, and I created the report and when I run it it shows up the correct fields I want...

Items collection restrict on created
Hi all, Is it possible to do a restrict on outlook calendar items according to the items [created] date/time? Regards, Asaf Yes, see http://www.outlookcode.com/d/finddate.htm . The field you want is CreationTime. Please do not cross-post. A post to one developer group should suffice. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "asaf lahav" <alahav@mecury.co.il> wrote in message news:uhvBnx%23OEHA.2876@TK2MSFTNGP09.phx.gbl... > ...

create a drop down list with the source from a different workbook
Hi, I tried to create a drop down list (Data > Validation > List) with the source from a different work book (If you type the list in a different workbook, define a name with an external reference to the list.). I followed the instructions given in the Microsoft Online Assistant. Iam not able to create the same as iam a getting a message stating that "You may not use references to other worksheets or workbooks for Data validation criteria." Try Debra Dalgleish's web site on this subject: http://www.contextures.com/tiptech.html Scroll down to "D", and see ...

Enter price to a Report from a table
I am having trouble getting my Invoice report to add the fee dependant on which option is selected. This works =IIf([Family Membership]=-1,535,"") but I do not want to manually change the 535 if the price changes. I created a table called fees and various fields. In this example I want it to enter the value of the "FamilyMembershipFees" field instead of the 535 I used in the IIF statement above. hi Arne, On 13.02.2010 13:53, ArneGolf wrote: > I am having trouble getting my Invoice report to add the fee dependant on > which option is selected. This wor...

adding a string to a GP field in Report Writer
Hello: I know how to create a conditional calculated field in Report Writer, but I have never done it for what I need here. I want to add three letters to the front of the PO number field in purchase orders in Report Writer. What is the syntax for this in the "True" case of the conditional calculated field? I tried "ABC" greatplainsponumberfield, but this cause a syntax error. Anyway, please let me know if you have any ideas on adding a string field in front of a GP field in GP 9.0 Report Writer. Thanks! childofthe1980s Hi childofthe1980s, You need to use the C...

CRM V3.0 on SBS prem cannot access reports
Hi.. currently have an issue with MS CRM with regard to accessing reports. from both client machine and server i cannot access reports. from within CRM i get "error.. An error has occurred. For more information, contact your system administrator." if i try to go to http://localhost/reports i get "The request failed with HTTP status 403: Forbidden" have looked on MS site and googled without much success. SBS 2003 prem (not R2 and no ISA) iis using .net v1.xxxx any idea thanks ...

proper way to detect if mouse button is depressed?
What is the proper way of decting whether a certain mouse button is depressed or not? If I create a class member bool m_rightbtndown and set it true on the message "OnRButtonDown" and set it false on "OnRButtonUp", that does not work if I right click in the window, and then drag the mouse outside the window, lift my finger, and then bring the mouse back to my window. my window still thinks the button is depressed because it never received "OnRButtonUp" How should I do this? nick You have to capture the mouse: void MyWnd::OnRButtonDown(....) { S...

Need quick way to build distribution list
Hi. I need to build a distribution list quickly which has as it's members people sending me emails (people not currently in my contacts). But the emails aren't coming from the individuals, they are coming from a form on my website. So - all the emails come from the same address (my web form) - all emails are formatted the same - all contain an email address for someone who filled out the form I'd like some kind of drag and drop or automated way to "dump" all those addressed into a list so I can reply to them en masse. Anyone know of a way to do this using ...

how to stop rules created by the asence assistent
HI NG, we use Exchange 2000 SP3. I allowed the users to send absence notices during their hildays, but disallowed forwarding rules. Although i ysecured the systenm by this way, the users find a possibility to forward emails through the absence assistent. I don=B4t want to forbid this, but is there a way to stop these rules at a central point of administration? Or do I need to disallow absence notices? Birte ...

Crystal Report
Any body knows about Crystal Report? I create de .def file. When I run it appear a Screen with "nothing" and Ok button ...

Print notes on purchase order blank report
How can I print the notes from purchase order header on the purchase order blank report? thank you in advance. You can modify the PO in Report Writer. Link the Records Notes Master to the Purchase Order HEader table and include the notes table in the report. Then the notes text field will be available to be displayed in the report. Regards Sivakumar V On Nov 17, 6:38 am, "Jack Tundra" <jacktun...@hotmail.com> wrote: > How can I print the notes from purchase order header on the purchase order > blank report? thank you in advance. Hi I am sorry but I cannot find th...

What date was a batch created
I'm in GP v8.0. I need to know what date a user created and/or saved a batch. I've looked all over and there doesn't seem to be any trail when a batch was created/saved short of turning on the User Tracking system. Please get back to me ASAP - sdanna@sfgmortgage.com First, as you probably know, this is not a forum for getting instant answers because it is not monitored officially. Second, SY00500 stores the batch history. It is in the company database. You will need to create a report based on this table. -- Charles Allen, MVP "Sandy D''Anna, GP Project Ma...

looking for xsl transform sample
This is a multi-part message in MIME format. ------=_NextPart_000_001E_01C4E029.11F709A0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable Hi,=20 is there a sample code for a program that accept input xml file name, = input xsl file name and output result file name and outputs the = resulting xml? TIA, z. ------=_NextPart_000_001E_01C4E029.11F709A0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HT...

Creating a parent/child relationship for vendors
Hello, Has anyone ever done a parent/child relationship for vendors in great plains? We have a structure that has multiple subcontractors working for the same vendor and since the number of contractors per vendor is a one to one relationship, we were thinking of creating a parent vendor and have the vendors associated with the contractors be the children. This way when we write up an invoice for the parent vendor, we can include all the child vendors information as well. I'm new to the world of Great Plains. If this is an absurd question, please excuse... Thanks, Med Look at the p...

I cannot access newly created users
Hi folks... I have using exchange server. i can add users in Active directory successfully. But I cannot logon through IE or Outllok.. That is from today onwards i can create users but When I configuring in Outlook or when i tried to login through IE I get the following error message. "Sorry. User id and password could not be verified. Please re-enter your user id and password. more details... javax.mail.AuthenticationFailedException: Logon failure: unknown user name or bad password." This means the My Exchange did not recognize the newly created user name. But while I ...

create an internal forum
I have been tasked with creating an internal discussion forum on an Exchange 2003 server. I am new to this and would appreciate any advice or tips that can be shared. I have been searching the internet for days and cannot figure this out. thanks in advance How fancy do you want to make it? You could just use a public folder and train users to sort messages by 'conversation'. Or you could create the public folder and build a web app with .net, or use sharepoint portal. "jason" wrote: > I have been tasked with creating an internal discussion forum on an Exchange &g...

Monthly Reports
Does anyone know how to do monthly reports via Cystral Reports via ODBC or using Excel via ODBC via HQ Database. You can connect the the SQL server via ODBC from Excel. Data | Database Query Create a new database source using the SQL Server driver, enter your server/database paramaters, and you are off the races. MS Query will open and you can create queries from there. Then you can slice and dice the data any way you want it or do Pivottables. Jason "TonyC" <TonyC@discussions.microsoft.com> wrote in message news:0FCBBBCD-121A-4A9B-B486-F7B57B532CC2@microsoft.com.....

"Dynamic message" on a report
I have been trying to figure out a way to do this and have not been able to find anything concrete to help me. I have a report containing several sub-reports. It is an inventory report for a variety of production lines. If the inventory on a certain die set is less then 0.5, I want to be able to display a message on the report telling the user that the inventory on this die set is low drawing attention to it. Can anyone provide an example of how I can accomplish this? I was thinking of an If, Then, Else If but have never seen it for a report. Any assistance would be appreciated. Thank yo...

Add number of days due field on aged trial balance reports (PM/RM)
I would like to add 'number of days past due' field on the aged trial balance reports and sort the reports by this field. I dont know how to proceed. Can someone guide me? ...

Lot matrix report
Is there a way to generate a report that would show all lot matrix items with the associated lots thanks Sure there is! If you have the custom matrix report already you simply need to change the value for "Class" (ItemClass.ClassType) to equal "2". This will show all Lot Matrix items and group them by the Lot Matrix Code. Let me know if you need additional help! CB "Robert" wrote: > Is there a way to generate a report that would show all lot matrix items > with the associated lots > > thanks > Where can i get the custom matrix r...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...

First hit to Reporting services is slow
Hello, [SQL2008] - version 10.0.2531 The first hit to our SQL2008 Reporting service is always very slow. Its a VMware and the total server memory is 7GB out of which we have reserved 4GB for SQLServer and 1.5GB for Reporting services. This is the entry snippet in rsreportserver config file: <MemorySafetyMargin>50</MemorySafetyMargin> <MemoryThreshold>75</MemoryThreshold> <WorkingSetMaximum>1572864</WorkingSetMaximum> <WorkingSetMinimum>1048576</WorkingSetMinimum> <RecycleTime>720</RecycleTime> S...

creating minimal DTD
Hi, is it possible to create a minimal DTD that can contain any not declared tag or text? With "minimal" I mean with the declaration of the root (for example "myRoot") and the declaration of entities (for example &egrave; &agrave; and similar) only. I've tried using the keyword ANY but it didn't work (ANY require the declaration of the used tag) Any idea? Thanks in advance. Fede Hello! > is it possible to create a minimal DTD that can contain any not declared tag > or text? > With "minimal" I mean with the declaration of the ro...