Strange behavior in Excel query

[Budgetguy]

I hope I'm choosing the correct forum for this question. I have about 50 Excel spreadsheets that pull information from FoxPro tables through MS Query. The spreadsheets are used once a year and have been used for years. When the query is edited to change the fiscal year only 8 colums of data is displayed initially. When the FY is changed you can see all 19 columns of data it is supposed to pull and the data looks correct. When you hit File/Return Data to Microsoft Excel 11 colums of data are dropped out and only the original 8 columns are displayed instead of the needed 19. There are no protected cells. The query is set to insert new data and delete unused cells. We've tried editing the query and deleting and recreating the query with no results. Please somebody save me from completely recreating 50 spreadsheets.

Thanks

 

[xlbo]

Querytable Properties - set 'Preserve Column Sort/Filter/Layout' to false (unticked)

If that doesn't work, please post a sample of the SQL

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Budgetguy]

Sorry for the delay. I set the 'Preserve Colunm Sort/Filter/Layout' to false and there was no change in the result. It still dropped data. This is the SQL.

SELECT fund.taxdist, fund.county, bud.fund, bud.cc, bud.revexp, bud.occ, bud.oproj, bud.supcode, bud.acct, objcatstcls.objname, objcatstcls.cat, objcatstcls.catname, objcatstcls.stcls, objcatstcls.stateclsnm, Sum(bud.act) AS 'Sum of act', Sum(bud.estcurbud) AS 'Sum of estcurbud', Sum(bud.recbud) AS 'Sum of recbud', bud.func, stpub.stpub
FROM bud bud, fund fund, objcatstcls objcatstcls, stpub stpub
WHERE fund.fund = bud.fund AND objcatstcls.acct = bud.acct AND stpub.stpub = fund.stpub
GROUP BY fund.taxdist, fund.county, bud.fund, bud.cc, bud.revexp, bud.occ, bud.oproj, bud.supcode, bud.acct, objcatstcls.objname, objcatstcls.cat, objcatstcls.catname, objcatstcls.stcls, objcatstcls.stateclsnm, bud.func, stpub.stpub, bud.fiscyear, bud.county
HAVING (bud.fund Between '1010' And '1150') AND (bud.revexp='1') AND (bud.fiscyear='2006') AND (bud.county='1')
ORDER BY objcatstcls.stcls, bud.cc, bud.occ, objcatstcls.cat, bud.acct, bud.oproj

 

[xlbo]

Does the original query (before changing the Fin Year) use the HAVING clause ??

Can't see any other reason for this - normally it is the Preserve order option that causes this kinda thing

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

if you know VBA then try amending the 'PreserveColumnInfo' property of the querytable - it is meant to be the same as the option on the QT properties but programmatic setting of properties has been known to create subtly different effects than setting them manually

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Budgetguy]

These were all set up long before I took a position in the department. The person I replaced was more of a programmer/tech person and I'm more of a numbers analyst. The original query does have the HAVING clause. I have some VBA knowledge and will take a shot at the 'PreserveColumnInfo' property. I'm offsite today and probably will be for the next few days so it will probably be late next week before I get to try to do anything else. Thanks for the suggestions.

 

[SkipVought]

I have found that the Query Wizard sticks thins in the AHVING clause that don't work as well as they would int the WHERE clause. I have typically ONLY used the HAVING clause for cirteria that involve aggregated functions.

Try this...

SELECT fund.taxdist, fund.county, bud.fund, bud.cc, bud.revexp, bud.occ, bud.oproj, bud.supcode, bud.acct, objcatstcls.objname, objcatstcls.cat, objcatstcls.catname, objcatstcls.stcls, objcatstcls.stateclsnm, Sum(bud.act) AS 'Sum of act', Sum(bud.estcurbud) AS 'Sum of estcurbud', Sum(bud.recbud) AS 'Sum of recbud', bud.func, stpub.stpub
FROM bud bud, fund fund, objcatstcls objcatstcls, stpub stpub
WHERE fund.fund = bud.fund AND objcatstcls.acct = bud.acct AND stpub.stpub = fund.stpub
[b]
AND (bud.fund Between '1010' And '1150') AND (bud.revexp='1') AND (bud.fiscyear='2006') AND (bud.county='1')
[/b]
GROUP BY fund.taxdist, fund.county, bud.fund, bud.cc, bud.revexp, bud.occ, bud.oproj, bud.supcode, bud.acct, objcatstcls.objname, objcatstcls.cat, objcatstcls.catname, objcatstcls.stcls, objcatstcls.stateclsnm, bud.func, stpub.stpub, bud.fiscyear, bud.county
ORDER BY objcatstcls.stcls, bud.cc, bud.occ, objcatstcls.cat, bud.acct, bud.oproj

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[Budgetguy]

I've figured out the what and the work-around and if I can figure out the why I'll be a happy man. I attempted the suggested changes in the SQL without any change in the outcome. Next I assumed some sort of corruption in the data and reviewed large samples without finding any problems. As a last resort I started deleting tables from the query to see what would happen. When OBJCATSTCLS was deleted the rest of the data would import into Excel through MS Query. It turns out that OBJCATSTCLS is actually a FoxPro .vue. I tried creating a new .vue and using it in my query and got the same results as with the original file. When I deleted the .vue file and replace it in the query with the actual tables the query worked perfectly. Thank you for your suggestions and if I figure out why this happened I'll post it here.

 

[xlbo]

Thanks for posting back - that sure does sound like an obscure problem !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question