Split query to export to Excel

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Hi-

Not sure if this should be posted in Query questions or Import/Export. Here
is the issue:
I have a manager who wants to see data in Excel, but doesn't understand the
65K limit. I am currently querying data by state and exporting to Excel,
however, some queries return upwards of 100K records.
The table I am using is joined to a member count query to give me members
per provider, and I use this to determine over/under 30 members.
What I'm having problems with is an accurate way to "split" a query to show
approx. half of the total records.
If anyone has had this problem I would certainly appreciate any insight. I
am using 2003.
Please see SQL below, and thanks in advance for the help!

SELECT COMB42_CHIMKT.Market, COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth, COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar, COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
FROM COMB42_CHIMKT
INNER JOIN qry42_OP_AC_MemCount ON COMB42_CHIMKT.Grp_Prov_TIN =
qry42_OP_AC_MemCount.Grp_Prov_TIN
GROUP BY COMB42_CHIMKT.Market, COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth, COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar, COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
HAVING (((COMB42_CHIMKT.Grp_ProvState)="IA") AND
((COMB42_CHIMKT.HCC_Status)="OPEN" Or (COMB42_CHIMKT.HCC_Status)="ACCEPTED")
AND ((qry42_OP_AC_MemCount.CountOfMemberUMID)>=30))
ORDER BY COMB42_CHIMKT.Grp_Prov_TIN, COMB42_CHIMKT.MemberUMID;
 
Managers! More like Damagers! Convince Damagement to upgrade to Office 2007
Professional. The limit for Excel is a million rows in it.

I wouldn't even try to evenly split the query. Instead why not use the first
letter of a name field to somewhat evenly divide things. Something like:

SELECT DISTINCT COMB42_CHIMKT.Market,
COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth,
COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar,
COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
FROM COMB42_CHIMKT
INNER JOIN qry42_OP_AC_MemCount ON COMB42_CHIMKT.Grp_Prov_TIN =
qry42_OP_AC_MemCount.Grp_Prov_TIN
WHERE (((COMB42_CHIMKT.Grp_ProvState)="IA")
AND ((COMB42_CHIMKT.HCC_Status)="OPEN"
Or (COMB42_CHIMKT.HCC_Status)="ACCEPTED")
AND ((qry42_OP_AC_MemCount.CountOfMemberUMID)>=30))
AND Left(COMB42_CHIMKT.MemberName,1) Like "[A-M]";
ORDER BY COMB42_CHIMKT.Grp_Prov_TIN, COMB42_CHIMKT.MemberUMID;

Notice that I simplified things by taking away the Group By and replacing it
with the DISTINCT clause to get rid of duplicates. If this doesn't work
right, go back to your method.

To get the other records, change the Like to Not Like. You could run these
queries and fine tune things by changing A-M to to something like A-N or A-K.
 
Managers! More like Damagers! Convince Damagement to upgrade to Office 2007
Professional. The limit for Excel is a million rows in it.
Click to expand...

I would suggest this, however, as soon as we upgrade the next report they
want will have 1.5 mil records! It just couldn't happen any other way..lol.
I wouldn't even try to evenly split the query. Instead why not use the first
letter of a name field to somewhat evenly divide things.
Click to expand...

This is a great idea and I tried it using the member name field. The
problem I ran into was inaccurate member counts. My MemberCount query is
pulling ALL members associated with a GroupTIN (per management), limiting to
A-M throws off the counts. I did try this by provider name, but ran into the
same issue due to multiple providers using the same TIN ex. Provider's ABC
Medical and XYZ Medical could use the same TIN, but would show up on
different sides of the query.

I thought about trying to display the top half of all unique GroupTIN's, but
have no idea if this is even possible. Suppose a query returns 100K records
and that encompasses 100 different GroupTIN's, would it be possible to get
the first 50 TIN's?
Of course, I would have no way of knowing how many TIN's each query would
return.
Thanks again for your assistance.
Jay
Jerry Whittle said:
Managers! More like Damagers! Convince Damagement to upgrade to Office 2007
Professional. The limit for Excel is a million rows in it.

I wouldn't even try to evenly split the query. Instead why not use the first
letter of a name field to somewhat evenly divide things. Something like:

SELECT DISTINCT COMB42_CHIMKT.Market,
COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth,
COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar,
COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
FROM COMB42_CHIMKT
INNER JOIN qry42_OP_AC_MemCount ON COMB42_CHIMKT.Grp_Prov_TIN =
qry42_OP_AC_MemCount.Grp_Prov_TIN
WHERE (((COMB42_CHIMKT.Grp_ProvState)="IA")
AND ((COMB42_CHIMKT.HCC_Status)="OPEN"
Or (COMB42_CHIMKT.HCC_Status)="ACCEPTED")
AND ((qry42_OP_AC_MemCount.CountOfMemberUMID)>=30))
AND Left(COMB42_CHIMKT.MemberName,1) Like "[A-M]";
ORDER BY COMB42_CHIMKT.Grp_Prov_TIN, COMB42_CHIMKT.MemberUMID;

Notice that I simplified things by taking away the Group By and replacing it
with the DISTINCT clause to get rid of duplicates. If this doesn't work
right, go back to your method.

To get the other records, change the Like to Not Like. You could run these
queries and fine tune things by changing A-M to to something like A-N or A-K.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jay said:
Hi-

Not sure if this should be posted in Query questions or Import/Export. Here
is the issue:
I have a manager who wants to see data in Excel, but doesn't understand the
65K limit. I am currently querying data by state and exporting to Excel,
however, some queries return upwards of 100K records.
The table I am using is joined to a member count query to give me members
per provider, and I use this to determine over/under 30 members.
What I'm having problems with is an accurate way to "split" a query to show
approx. half of the total records.
If anyone has had this problem I would certainly appreciate any insight. I
am using 2003.
Please see SQL below, and thanks in advance for the help!

SELECT COMB42_CHIMKT.Market, COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth, COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar, COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
FROM COMB42_CHIMKT
INNER JOIN qry42_OP_AC_MemCount ON COMB42_CHIMKT.Grp_Prov_TIN =
qry42_OP_AC_MemCount.Grp_Prov_TIN
GROUP BY COMB42_CHIMKT.Market, COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth, COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar, COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
HAVING (((COMB42_CHIMKT.Grp_ProvState)="IA") AND
((COMB42_CHIMKT.HCC_Status)="OPEN" Or (COMB42_CHIMKT.HCC_Status)="ACCEPTED")
AND ((qry42_OP_AC_MemCount.CountOfMemberUMID)>=30))
ORDER BY COMB42_CHIMKT.Grp_Prov_TIN, COMB42_CHIMKT.MemberUMID;
Click to expand...
Click to expand...
 
You could create the query in Excel to actually pull the data from your
Access database. Then you can setup separate tabs for certain segments of
your data (A-F, G-Q, R-Z).

Or, if you are not worried about speed, you could open Excel using
automation, and write your data one line at a time to the Excel file. When
you reach your 65K limit, you could move the focus to Sheet2 of the
spreadsheet and continue the export, jumpint to Sheet3, 4, 5, ... as
necessary.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Jay said:
Managers! More like Damagers! Convince Damagement to upgrade to Office 2007
Professional. The limit for Excel is a million rows in it.
Click to expand...

I would suggest this, however, as soon as we upgrade the next report they
want will have 1.5 mil records! It just couldn't happen any other way..lol.
I wouldn't even try to evenly split the query. Instead why not use the first
letter of a name field to somewhat evenly divide things.
Click to expand...

This is a great idea and I tried it using the member name field. The
problem I ran into was inaccurate member counts. My MemberCount query is
pulling ALL members associated with a GroupTIN (per management), limiting to
A-M throws off the counts. I did try this by provider name, but ran into the
same issue due to multiple providers using the same TIN ex. Provider's ABC
Medical and XYZ Medical could use the same TIN, but would show up on
different sides of the query.

I thought about trying to display the top half of all unique GroupTIN's, but
have no idea if this is even possible. Suppose a query returns 100K records
and that encompasses 100 different GroupTIN's, would it be possible to get
the first 50 TIN's?
Of course, I would have no way of knowing how many TIN's each query would
return.
Thanks again for your assistance.
Jay
Jerry Whittle said:
Managers! More like Damagers! Convince Damagement to upgrade to Office 2007
Professional. The limit for Excel is a million rows in it.

I wouldn't even try to evenly split the query. Instead why not use the first
letter of a name field to somewhat evenly divide things. Something like:

SELECT DISTINCT COMB42_CHIMKT.Market,
COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth,
COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar,
COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
FROM COMB42_CHIMKT
INNER JOIN qry42_OP_AC_MemCount ON COMB42_CHIMKT.Grp_Prov_TIN =
qry42_OP_AC_MemCount.Grp_Prov_TIN
WHERE (((COMB42_CHIMKT.Grp_ProvState)="IA")
AND ((COMB42_CHIMKT.HCC_Status)="OPEN"
Or (COMB42_CHIMKT.HCC_Status)="ACCEPTED")
AND ((qry42_OP_AC_MemCount.CountOfMemberUMID)>=30))
AND Left(COMB42_CHIMKT.MemberName,1) Like "[A-M]";
ORDER BY COMB42_CHIMKT.Grp_Prov_TIN, COMB42_CHIMKT.MemberUMID;

Notice that I simplified things by taking away the Group By and replacing it
with the DISTINCT clause to get rid of duplicates. If this doesn't work
right, go back to your method.

To get the other records, change the Like to Not Like. You could run these
queries and fine tune things by changing A-M to to something like A-N or A-K.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jay said:
Hi-

Not sure if this should be posted in Query questions or Import/Export. Here
is the issue:
I have a manager who wants to see data in Excel, but doesn't understand the
65K limit. I am currently querying data by state and exporting to Excel,
however, some queries return upwards of 100K records.
The table I am using is joined to a member count query to give me members
per provider, and I use this to determine over/under 30 members.
What I'm having problems with is an accurate way to "split" a query to show
approx. half of the total records.
If anyone has had this problem I would certainly appreciate any insight. I
am using 2003.
Please see SQL below, and thanks in advance for the help!

SELECT COMB42_CHIMKT.Market, COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth, COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar, COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
FROM COMB42_CHIMKT
INNER JOIN qry42_OP_AC_MemCount ON COMB42_CHIMKT.Grp_Prov_TIN =
qry42_OP_AC_MemCount.Grp_Prov_TIN
GROUP BY COMB42_CHIMKT.Market, COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth, COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar, COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
HAVING (((COMB42_CHIMKT.Grp_ProvState)="IA") AND
((COMB42_CHIMKT.HCC_Status)="OPEN" Or (COMB42_CHIMKT.HCC_Status)="ACCEPTED")
AND ((qry42_OP_AC_MemCount.CountOfMemberUMID)>=30))
ORDER BY COMB42_CHIMKT.Grp_Prov_TIN, COMB42_CHIMKT.MemberUMID;
Click to expand...
Click to expand...
Click to expand...
 
You could create the query in Excel to actually pull the data from your
Access database. Then you can setup separate tabs for certain segments of
your data (A-F, G-Q, R-Z).
Click to expand...

How does this solve the issue of inaccurate member counts? If I query by
alpha I am not getting ALL members associated with a Group TIN.
Or, if you are not worried about speed, you could open Excel using
automation, and write your data one line at a time to the Excel file. When
you reach your 65K limit, you could move the focus to Sheet2 of the
spreadsheet and continue the export, jumpint to Sheet3, 4, 5, ... as
necessary.
Click to expand...

I have tried something like this I found on Chip Pearson's site,
http://www.cpearson.com/excel/ImportBigFiles.aspx, but have not been able to
get it to work. I've been working on importing a text file to Excel and
having it automatically jump to sheet 2 when full, but am admittedly a novice
when it comes to VBA.


Dale Fye said:
You could create the query in Excel to actually pull the data from your
Access database. Then you can setup separate tabs for certain segments of
your data (A-F, G-Q, R-Z).

Or, if you are not worried about speed, you could open Excel using
automation, and write your data one line at a time to the Excel file. When
you reach your 65K limit, you could move the focus to Sheet2 of the
spreadsheet and continue the export, jumpint to Sheet3, 4, 5, ... as
necessary.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Jay said:
Managers! More like Damagers! Convince Damagement to upgrade to Office 2007
Professional. The limit for Excel is a million rows in it.
Click to expand...

I would suggest this, however, as soon as we upgrade the next report they
want will have 1.5 mil records! It just couldn't happen any other way..lol.
I wouldn't even try to evenly split the query. Instead why not use the first
letter of a name field to somewhat evenly divide things.
Click to expand...

This is a great idea and I tried it using the member name field. The
problem I ran into was inaccurate member counts. My MemberCount query is
pulling ALL members associated with a GroupTIN (per management), limiting to
A-M throws off the counts. I did try this by provider name, but ran into the
same issue due to multiple providers using the same TIN ex. Provider's ABC
Medical and XYZ Medical could use the same TIN, but would show up on
different sides of the query.

I thought about trying to display the top half of all unique GroupTIN's, but
have no idea if this is even possible. Suppose a query returns 100K records
and that encompasses 100 different GroupTIN's, would it be possible to get
the first 50 TIN's?
Of course, I would have no way of knowing how many TIN's each query would
return.
Thanks again for your assistance.
Jay
Jerry Whittle said:
Managers! More like Damagers! Convince Damagement to upgrade to Office 2007
Professional. The limit for Excel is a million rows in it.

I wouldn't even try to evenly split the query. Instead why not use the first
letter of a name field to somewhat evenly divide things. Something like:

SELECT DISTINCT COMB42_CHIMKT.Market,
COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth,
COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar,
COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
FROM COMB42_CHIMKT
INNER JOIN qry42_OP_AC_MemCount ON COMB42_CHIMKT.Grp_Prov_TIN =
qry42_OP_AC_MemCount.Grp_Prov_TIN
WHERE (((COMB42_CHIMKT.Grp_ProvState)="IA")
AND ((COMB42_CHIMKT.HCC_Status)="OPEN"
Or (COMB42_CHIMKT.HCC_Status)="ACCEPTED")
AND ((qry42_OP_AC_MemCount.CountOfMemberUMID)>=30))
AND Left(COMB42_CHIMKT.MemberName,1) Like "[A-M]";
ORDER BY COMB42_CHIMKT.Grp_Prov_TIN, COMB42_CHIMKT.MemberUMID;

Notice that I simplified things by taking away the Group By and replacing it
with the DISTINCT clause to get rid of duplicates. If this doesn't work
right, go back to your method.

To get the other records, change the Like to Not Like. You could run these
queries and fine tune things by changing A-M to to something like A-N or A-K.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi-

Not sure if this should be posted in Query questions or Import/Export. Here
is the issue:
I have a manager who wants to see data in Excel, but doesn't understand the
65K limit. I am currently querying data by state and exporting to Excel,
however, some queries return upwards of 100K records.
The table I am using is joined to a member count query to give me members
per provider, and I use this to determine over/under 30 members.
What I'm having problems with is an accurate way to "split" a query to show
approx. half of the total records.
If anyone has had this problem I would certainly appreciate any insight. I
am using 2003.
Please see SQL below, and thanks in advance for the help!

SELECT COMB42_CHIMKT.Market, COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth, COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar, COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
FROM COMB42_CHIMKT
INNER JOIN qry42_OP_AC_MemCount ON COMB42_CHIMKT.Grp_Prov_TIN =
qry42_OP_AC_MemCount.Grp_Prov_TIN
GROUP BY COMB42_CHIMKT.Market, COMB42_CHIMKT.Grp_Prov_TIN,
COMB42_CHIMKT.Grp_ProviderID, COMB42_CHIMKT.Grp_ProvName,
COMB42_CHIMKT.Grp_ProvPhone, COMB42_CHIMKT.Grp_ProvAddress,
COMB42_CHIMKT.Grp_ProvCity, COMB42_CHIMKT.Grp_ProvState,
COMB42_CHIMKT.Grp_ProvZip, COMB42_CHIMKT.MemberName,
COMB42_CHIMKT.MemberUMID, COMB42_CHIMKT.DateOfBirth, COMB42_CHIMKT.HCC,
COMB42_CHIMKT.HCC_Desc, COMB42_CHIMKT.HCC_Status,
COMB42_CHIMKT.ProvSpecialty, COMB42_CHIMKT.Rnd_ProviderID,
COMB42_CHIMKT.Rnd_ProvName, COMB42_CHIMKT.Rnd_ProvPhone,
COMB42_CHIMKT.Rnd_ProvAddress1, COMB42_CHIMKT.Rnd_ProvAddress2,
COMB42_CHIMKT.Rnd_ProvAddress3, COMB42_CHIMKT.Rnd_ProvCity,
COMB42_CHIMKT.Rnd_ProvState, COMB42_CHIMKT.Rnd_ProvZip,
COMB42_CHIMKT.Claim_Src, COMB42_CHIMKT.Rnd_Prov_TIN,
COMB42_CHIMKT.Cond_Status, COMB42_CHIMKT.High_Dollar, COMB42_CHIMKT.CMS_Year,
qry42_OP_AC_MemCount.CountOfMemberUMID
HAVING (((COMB42_CHIMKT.Grp_ProvState)="IA") AND
((COMB42_CHIMKT.HCC_Status)="OPEN" Or (COMB42_CHIMKT.HCC_Status)="ACCEPTED")
AND ((qry42_OP_AC_MemCount.CountOfMemberUMID)>=30))
ORDER BY COMB42_CHIMKT.Grp_Prov_TIN, COMB42_CHIMKT.MemberUMID;
Click to expand...
Click to expand...
Click to expand...
 
Hi Jay,
here is a way of showing data in excel without exporting it.
--create the query in Access
--open a new file in excel
--chooose Data > Import External Data >Import Data
--in the dialog box that opens navigate to your query in Access
--follow the prompts as excel guides you through the process
--when the worksheet is finished, there is a small toolbar for refreshing
(updating) the worksheet with the latest data from the Access query.

Jeanette Cunningham
 
Hi Jeanette-

Your suggestion works great, and I will probably implement it for future
projects, however, I am having difficulty with the amount of records my query
returns. For example, when I query just Iowa, I get 80K+ records. Using
your suggestion I get the error "Query returned more data than will fit on a
wroksheet".
Thanks again for the reply.

-Jay
 
Back
Top