Selective counting of database entries

[Herbert Eppel]

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

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?

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

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]

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?

The resolution above eliminates exporting all together.

Sounds like a very elegant solution - I just hope I'll manage to
implement it ;-)

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

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

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.

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


Sounds like a very elegant solution - I just hope I'll manage to
implement it ;-)

Good Luck

 

[Herbert Eppel]

:

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:

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!

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

Good Luck

I think I'll need it ;-)

Thanks for pointing me in the right direction.