Selective counting of database entries
Herbert Eppel
Vu from Atril - see www.atril.com) is based on MS Access (97 / 2000) -
see previous discussion at
<http://groups.google.com/group/microsoft.public.access/browse_frm/thread/f5dead96c1d3c65d/>.
The translation memory databases have 'Client' and 'Subject' attributes,
among others.
I would like to count the number of entries contained in my main
database for specific client/subject combinations (and for some clients
I would also like to count the number of words).
I assumed that this would possible from within Déjà Vu (using SQL), but
it seems the only way to do this would be via selective exports, which
would be somewhat cumbersome.
A colleague suggested that it might be possible by opening the database
in Access and creating "Queries", but I'm not sure where to go from here.
Any suggestions?
--
Herbert Eppel
www.HETranslation.co.uk
Jeff C
--
Jeff C
Live Well .. Be Happy In All You Do
"Herbert Eppel" wrote:
> Some of you may remember that my main translation memory software (Déjà
> Vu from Atril - see www.atril.com) is based on MS Access (97 / 2000) -
> see previous discussion at
> <http://groups.google.com/group/microsoft.public.access/browse_frm/thread/f5dead96c1d3c65d/>.
>
> The translation memory databases have 'Client' and 'Subject' attributes,
> among others.
>
> I would like to count the number of entries contained in my main
> database for specific client/subject combinations (and for some clients
> I would also like to count the number of words).
One quick way to do this would be by using Excel. Data, import data, and
create a UDBC link to your data table. Once you have completed this and have
the established link, opening the workbook to that sheet and using the Data
Refresh, you will always have current data to work from.
Then create a pivot table to analyze this data counting the combinations you
are interested in. Once in your pivot table I think you will find it easier
to group the unique combinations you are trying to count, as opposed to
defining these combinations as criteria for an Access/SQL query to count.
>
> I assumed that this would possible from within Déjà Vu (using SQL), but
> it seems the only way to do this would be via selective exports, which
> would be somewhat cumbersome.
The resolution above eliminates exporting all together.
> A colleague suggested that it might be possible by opening the database
> in Access and creating "Queries", but I'm not sure where to go from here.
Certainly this can be done, but if you are unfamiliar with building queries,
trying to define and count consistent data can be very challenging.
(Certainly worth the effort to learn though).
Herbert Eppel
> "Herbert Eppel" wrote:
>> > Some of you may remember that my main translation memory software (Déjà
>> > Vu from Atril - see www.atril.com) is based on MS Access (97 / 2000) -
>> > see previous discussion at
>> > <http://groups.google.com/group/microsoft.public.access/browse_frm/thread/f5dead96c1d3c65d/>.
>> >
>> > The translation memory databases have 'Client' and 'Subject' attributes,
>> > among others.
>> >
>> > I would like to count the number of entries contained in my main
>> > database for specific client/subject combinations (and for some clients
>> > I would also like to count the number of words).
>
> One quick way to do this would be by using Excel. Data, import data, and
> create a UDBC link to your data table. Once you have completed this and have
> the established link, opening the workbook to that sheet and using the Data
> Refresh, you will always have current data to work from.
Hi Jeff
Thanks for your reply.
I'm afraid I'm not familiar with UDBC links in Excel (perhaps I better
study the online help?)
Am I right in assuming that the data wouldn't actually be imported into
Excel? (The database is too big for import into Excel).
> Then create a pivot table to analyze this data counting the combinations you
> are interested in. Once in your pivot table I think you will find it easier
> to group the unique combinations you are trying to count, as opposed to
> defining these combinations as criteria for an Access/SQL query to count.
I'll have to admit ignorance again, i.e. I'm not familiar with the pivot
table concept. Should I be able to get the hang of it from the online help?
Would follow-up questions on this aspect be more appropriate for an
Excel newsgroup or is it OK to carry on discussing it here?
>
>> >
>> > I assumed that this would possible from within Déjà Vu (using SQL), but
>> > it seems the only way to do this would be via selective exports, which
>> > would be somewhat cumbersome.
>
> The resolution above eliminates exporting all together.
Sounds like a very elegant solution - I just hope I'll manage to
implement it ;-)
>
>> > A colleague suggested that it might be possible by opening the database
>> > in Access and creating "Queries", but I'm not sure where to go from here.
>
> Certainly this can be done, but if you are unfamiliar with building queries,
> trying to define and count consistent data can be very challenging.
> (Certainly worth the effort to learn though).
It seems I have quite a bit of learning to do even without trying to get
into queries! =-O
--
Herbert Eppel
www.HETranslation.co.uk
Jeff C
--
Jeff C
Live Well .. Be Happy In All You Do
"Herbert Eppel" wrote:
> On 17.01.2008 10:15 UK Time, Jeff C wrote:
>
> > "Herbert Eppel" wrote:
> >> > Some of you may remember that my main translation memory software (Déjà
> >> > Vu from Atril - see www.atril.com) is based on MS Access (97 / 2000) -
> >> > see previous discussion at
> >> > <http://groups.google.com/group/microsoft.public.access/browse_frm/thread/f5dead96c1d3c65d/>.
> >> >
> >> > The translation memory databases have 'Client' and 'Subject' attributes,
> >> > among others.
> >> >
> >> > I would like to count the number of entries contained in my main
> >> > database for specific client/subject combinations (and for some clients
> >> > I would also like to count the number of words).
> >
> > One quick way to do this would be by using Excel. Data, import data, and
> > create a UDBC link to your data table. Once you have completed this and have
> > the established link, opening the workbook to that sheet and using the Data
> > Refresh, you will always have current data to work from.
>
> Hi Jeff
>
> Thanks for your reply.
>
> I'm afraid I'm not familiar with UDBC links in Excel (perhaps I better
> study the online help?)
If you go to the Excel forum and do a thorough search there are sure to be
many posts completely outlining the steps, it really is not difficult once
you step through the process.
> Am I right in assuming that the data wouldn't actually be imported into
> Excel? (The database is too big for import into Excel).
>
While Excel has its limitations, Excel also has unlimited (nearly) cache
memory it works with. When using the linking method the size limitations are
eliminated.
> > Then create a pivot table to analyze this data counting the combinations you
> > are interested in. Once in your pivot table I think you will find it easier
> > to group the unique combinations you are trying to count, as opposed to
> > defining these combinations as criteria for an Access/SQL query to count.
>
> I'll have to admit ignorance again, i.e. I'm not familiar with the pivot
> table concept. Should I be able to get the hang of it from the online help?
>
Again, do a thorough search of the forum, there are posts that will describe
and step you through the process.
> Would follow-up questions on this aspect be more appropriate for an
> Excel newsgroup or is it OK to carry on discussing it here?
Yes
> >
> >> >
> >> > I assumed that this would possible from within Déjà Vu (using SQL), but
> >> > it seems the only way to do this would be via selective exports, which
> >> > would be somewhat cumbersome.
> >
> > The resolution above eliminates exporting all together.
>
> Sounds like a very elegant solution - I just hope I'll manage to
> implement it ;-)
Good Luck
Herbert Eppel
> "Herbert Eppel" wrote:
>> > I'm afraid I'm not familiar with UDBC links in Excel (perhaps I better
>> > study the online help?)
>
> If you go to the Excel forum and do a thorough search there are sure to be
> many posts completely outlining the steps, it really is not difficult once
> you step through the process.
Thanks, I'll do that, although I'm not quite sure which Excel forum is
most appropriate - there seem to be quite a few:
<http://groups.google.com/groups/dir?lnk=nhpsfg&q=microsoft.public.excel&qt_s=Search+for+a+group>
>
>> > Am I right in assuming that the data wouldn't actually be imported into
>> > Excel? (The database is too big for import into Excel).
>> >
> While Excel has its limitations, Excel also has unlimited (nearly) cache
> memory it works with. When using the linking method the size limitations are
> eliminated.
That sounds very promising indeed!
>
>>> > > Then create a pivot table to analyze this data counting the combinations you
>>> > > are interested in. Once in your pivot table I think you will find it easier
>>> > > to group the unique combinations you are trying to count, as opposed to
>>> > > defining these combinations as criteria for an Access/SQL query to count.
>> >
>> > I'll have to admit ignorance again, i.e. I'm not familiar with the pivot
>> > table concept. Should I be able to get the hang of it from the online help?
>> >
> Again, do a thorough search of the forum, there are posts that will describe
> and step you through the process.
OK, will do, once I know which Excel forum to choose :-)
>>> > > The resolution above eliminates exporting all together.
>> >
>> > Sounds like a very elegant solution - I just hope I'll manage to
>> > implement it ;-)
>
> Good Luck
I think I'll need it ;-)
Thanks for pointing me in the right direction.
--
Herbert Eppel
www.HETranslation.co.uk