Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Unanswered: Access 2003 - Filter By Selection/Form v. slow (30 mins+)

    I have a select query which is used as the data source for 3 forms.

    The query is a left join of two tables with some sorting but no filtering plus 5 calculations. One table contains data for which no edits are permitted. The second table holds all the data that can be edited. The Query takes a second or two to run (across a network) and brings back about 22,000 rows.

    The forms allow the users to view the data and edit it where permitted and the locking is at record-level on edit. The users want to filter the query to work on different groups of data and this is the problem area.

    The main form used is a DataSheet and the method of filtering is usually - Find a value, Right Click and choose Filter By Selection or sometimes Filter Excluding Selection. Occasionally they use filter By Form. ie this is using the standard Access filtering tools, no special code etc.

    Sometimes the filtering works immediately (sub-second response), other times it takes several seconds before anything starts to happen and 30 minutes (yes minutes) later the filtering is still not complete.
    Whether the filtering works or not seems to be completely random (ie I have not yet managed to isolate the circumstances which reliably cause it to fail and/or reliably succeed). It will work on a specific field one day and not the next.

    I have made the following tests:-
    Filter directly against the query (instead of against the form over the query) - this produced similar random results so I have (for the moment) discounted a problem with the Forms
    Filter directly against each table - this seems to work correctly all the time which implies it is something to do with the query.
    Re-write the Query from scratch - doesn't seem to have an effect
    Used a local copy of the database on my PC's hard drive - still get the same random results when filtering, so have discounted a network effect and a multi-user effect
    Re-did the table definitions to make sure there is nothing unusual. Checked the table properties to make sure filtering is enabled
    Made changes to the Tools Options settings for Filtering
    Tried explicitly removing sort/filter before reapplying in case something was being stored.
    Discussed with 2 other colleagues to get other thinking on the topic.

    The only things that seem to have come out so far are:-
    Filtering on data from the 'non-editable' table always seems to work.
    Filtering on editable data sometimes works and sometimes doesn't (even filtering on the same field for the same value)
    I have compared the properties and field settings between the two tables in the query to make sure they are the same.

    I have run out of ideas. Can anyone else come up with other avenues I can explore. Thanks for your help.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    30+ minutes?!

    Without seeing table structure, record counts and the query that is giving the problem, it's pretty hard to say much.

    I can only suggest things like Compact & Repairing the files, but I will give this some more thought.

    In the meantime, if you find the solution, please don't forget to post it here
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2008
    Posts
    3
    Unfortunately yes - when the filtering doesn't work properly it can be 30+ minutes and then doesn't complete. A second or two when it does work properly.

    The database is Compacted and Repaired on a daily basis as part of an overnight update. So I will do some more testing around that to see if the first filter works and then others don't.

    There are 22,444 records brought back from the query.

    Here's the SQL
    Code:
    SELECT tblQryF_NoCatDedup.*, tblPriceMgt.PlannedPrice,
    tblPriceMgt.QueryPlannedPrice, tblPriceMgt.NLPTicketFlag,
    tblPriceMgt.SupplierSupport, tblPriceMgt.CommentLog,
    tblPriceMgt.CommentLogDate, tblPriceMgt.BasketFlag2,
    tblPriceMgt.SubBasketFlag3, tblPriceMgt.SubBasketFlag4,
    tblPriceMgt.SubBasketFlag5, tblPriceMgt.OLMatchVerifiedFlag6,
    tblPriceMgt.NAMismatchFlag7, tblPriceMgt.NAFuturePromoFlag8,
    tblPriceMgt.NADeferredFlag9, tblPriceMgt.NAMktPriceMoveFlag10,
    tblPriceMgt.NAPricePtIssueFlag11, tblPriceMgt.NLPLinePhaseFlag12,
    tblPriceMgt.OutlierPct, tblPriceMgt.QDRatingFlag13,
    tblPriceMgt.PhysicalCheckFlag14, tblPriceMgt.TeamTastingTestFlag15,
    tblPriceMgt.BuyerQueryFlag16, tblPriceMgt.RupertBenchmarkFlag17,
    tblPriceMgt.IQListforRupertFlag18, tblPriceMgt.IQComment,
    tblPriceMgt.IQCommentDate, tblPriceMgt.ExcludeForANYreasonFlag19,
    tblPriceMgt.ExcludeForCostIncFlag20, tblPriceMgt.Flag21, tblPriceMgt.Flag22,
    tblPriceMgt.Flag23, tblPriceMgt.Flag24, tblPriceMgt.Flag25, tblPriceMgt.Flag26,
    tblPriceMgt.Flag27, tblPriceMgt.Flag28, tblPriceMgt.Flag29, tblPriceMgt.Flag30,
    tblPriceMgt.Created, tblPriceMgt.CreatedBy, tblPriceMgt.LastUpdate,
    blPriceMgt.LastUpdateBy, Round([COST_PRICE]/[CASE_SIZE],2) AS UnitCost,
    Round(ProRata([WrLineSizeAdj],[CompLineSizeAdj],[WrLineCountAdj],[CompLineCountAdj],[ProRatCode]),3) AS ProRataFactor,
    IIf([PREVIOUS_PRICE]>0,Round([PREVIOUS_PRICE]*(ProRata([WrLineSizeAdj],[CompLineSizeAdj],[WrLineCountAdj],[CompLineCountAdj],[ProRatCode])),2),IIf([PRICE]>0,Round([PRICE]*(ProRata([WrLineSizeAdj],[CompLineSizeAdj],[WrLineCountAdj],[CompLineCountAdj],[ProRatCode])),2),0)) AS ProRataPrice,
    IIf([PRICE]=0,0,Round((StdSP([BASIC_PMTY_BCODE],[SELLING_PRICE],[NonPromoPrice],[NextSellPriceAmt])/IIf([PREVIOUS_PRICE]>0,Round([PREVIOUS_PRICE]*(ProRata([WrLineSizeAdj],[CompLineSizeAdj],[WrLineCountAdj],[CompLineCountAdj],[ProRatCode])),2),IIf([PRICE]>0,Round([PRICE]*(ProRata([WrLineSizeAdj],[CompLineSizeAdj],[WrLineCountAdj],[CompLineCountAdj],[ProRatCode])),2),0))*100)-100,2)) AS PriceDiffPct,
    StdSP([BASIC_PMTY_BCODE],[SELLING_PRICE],[NonPromoPrice],[NextSellPriceAmt])-IIf([PREVIOUS_PRICE]>0,Round([PREVIOUS_PRICE]*(ProRata([WrLineSizeAdj],[CompLineSizeAdj],[WrLineCountAdj],[CompLineCountAdj],[ProRatCode])),2),IIf([PRICE]>0,Round([PRICE]*(ProRata([WrLineSizeAdj],[CompLineSizeAdj],[WrLineCountAdj],[CompLineCountAdj],[ProRatCode])),2),0)) AS PriceDiffAmt
    FROM tblQryF_NoCatDedup
    LEFT JOIN tblPriceMgt
    ON (tblQryF_NoCatDedup.CompLineCode = tblPriceMgt.CompLineCode) AND
    (tblQryF_NoCatDedup.CompCode = tblPriceMgt.CompCode) AND
    tblQryF_NoCatDedup.MatchType = tblPriceMgt.MatchType) AND 
    tblQryF_NoCatDedup.MatchBasis = tblPriceMgt.MatchBasis) AND
    (tblQryF_NoCatDedup.Line = tblPriceMgt.Line);
    Here are the table defs:
    Code:
    Table: tblPriceMgt	Page: 1
    	Properties
    	DatasheetFontHeight: 	8	DatasheetFontItalic: 	False
    	DatasheetFontName: 	Verdana	DatasheetFontUnderline: 	False
    	DatasheetFontWeight: 	Normal	DatasheetForeColor: 	33554432
    	DateCreated: 	07/02/2008 09:50:24	DefaultView: 	Datasheet
    	Description: 		GUID: 	{guid {EADA92DC-F72A-
    		440D-A6DA-
    	LastUpdated: 	01/05/2008 18:46:38	NameMap: 	Long binary data
    	OrderBy: 	tblPriceMgt.Line	OrderByOn: 	True
    	Orientation: 	Left-to-Right	RecordCount: 	12044
    	TabularCharSet: 	0	TabularFamily: 	34
    	Updatable: 	True
    	Columns
    	Name	Type	Size
    	Line	Long Integer	4
    	MatchBasis	Text	5
    	MatchType	Text	5
    	CompCode	Text	5
    	CompLineCode	Text	20
    	PlannedPrice	Decimal	16
    	QueryPlannedPrice	Text	1
    	NLPTicketFlag	Text	1
    	SupplierSupport	Text	255
    	CommentLog	Text	255
    	CommentLogDate	Date/Time	8
    	BasketFlag2	Text	20
    	SubBasketFlag3	Text	20
    	SubBasketFlag4	Text	20
    	SubBasketFlag5	Text	20
    	OLMatchVerifiedFlag6	Text	1
    	NAMismatchFlag7	Text	1
    	NAFuturePromoFlag8	Text	1
    	NADeferredFlag9	Text	1
    	NAMktPriceMoveFlag10	Text	1
    	NAPricePtIssueFlag11	Text	1
    	NLPLinePhaseFlag12	Text	25
    	OutlierPct	Decimal	16
    	QDRatingFlag13	Text	15
    	PhysicalCheckFlag14	Text	1
    	TeamTastingTestFlag15	Text	1
    	BuyerQueryFlag16	Text	1
    	RupertBenchmarkFlag17	Text	1
    	IQListforRupertFlag18	Text	1
    	IQComment	Text	255
    	IQCommentDate	Date/Time	8
    	ExcludeForANYreasonFlag19	Text	1
    	ExcludeForCostIncFlag20	Text	1
    Code:
    Table: tblPriceMgt	Page: 2
    	Flag21	Text	1
    	Flag22	Text	1
    	Flag23	Text	1
    	Flag24	Text	1
    	Flag25	Text	1
    	Flag26	Text	1
    	Flag27	Text	1
    	Flag28	Text	1
    	Flag29	Text	1
    	Flag30	Text	1
    	Created	Date/Time	8
    	CreatedBy	Text	15
    	LastUpdate	Date/Time	8
    	LastUpdateBy	Text	15
    	Table Indexes
    	Name	Number of Fields
    	CompCode	1
    	Fields:
    	CompCode	Ascending
    	CompProdCode	1
    	Fields:
    	CompLineCode	Ascending
    	MatchBasis	1
    	Fields:
    	MatchBasis	Ascending
    	PrimaryKey	5
    	Fields:
    	Line	Ascending
    	MatchBasis	Ascending
    	MatchType	Ascending
    	CompCode	Ascending
    	CompLineCode	Ascending
    	Line	1
    	Fields:
    	Line	Ascending
    Code:
    Table: tblQryF_NoCatDedup	Page: 3
    	Properties
    	DatasheetFontHeight: 	8	DatasheetFontItalic: 	False
    	DatasheetFontName: 	Verdana	DatasheetFontUnderline: 	False
    	DatasheetFontWeight: 	Normal	DatasheetForeColor: 	33554432
    	DateCreated: 	28/05/2008 12:17:57	DefaultView: 	Datasheet
    	Description: 		LastUpdated: 	30/05/2008 11:23:27
    	OrderByOn: 	True	Orientation: 	Left-to-Right
    	RecordCount: 	11917	TabularCharSet: 	0
    	TabularFamily: 	34	Updatable: 	True
    	Columns
    	Name	Type	Size
    	Line	Long Integer	4
    	MatchBasis	Text	5
    	MatchType	Text	5
    	CompCode	Text	5
    	CompLineCode	Text	20
    	MatchStatus	Text	10
    	MatchQF	Text	5
    	MatchSC	Text	5
    	MBrand	Text	5
    	ProRatCode	Text	5
    	LineSizeAdj	Decimal	16
    	LineCountAdj	Long Integer	4
    	CompLineSizeAdj	Decimal	16
    	CompLineCountAdj	Long Integer	4
    	LONGNAME	Text	50
    	MAIN_CUC_BARCODE	Decimal	16
    	BUY_GRP_PCODE	Text	3
    	BUY_OFF_PCODE	Text	3
    	BUY_OFF_BU_NUM	Long Integer	4
    	BO_SECTION_PCODE	Text	6
    	BO_SUBSECT_PCODE	Text	6
    	MAIN_SUPP_NUM	Long Integer	4
    	MAIN_SUPP_NAME10	Text	10
    	OWN_LABEL_IND	Text	1
    	ORGANIC_IND	Text	1
    	SHOP_BASKET_IND	Text	1
    	LIVE_IND	Text	1
    	SV_COUNTER_IND	Text	1
    	LCL_RGNL_ASST_IND	Text	1
    	SELLING_PRICE	Decimal	16
    	STATUS_PCODE	Text	7
    	BASIC_PMTY_BCODE	Text	6
    	OFFER_TYPE_BCODE	Text	6
    	SELLING_PRICE_QTY	Decimal	16
    	SELLING_PRICE_PUOM	Text	6
    	SIZE_DESC15	Text	15
    Code:
    Table: tblQryF_NoCatDedup	Page: 4
    	LAUNCH_DATE	Date/Time	8
    	DELETE_PEND_DATE	Date/Time	8
    	DELETE_DATE	Date/Time	8
    	HEAD_OFF_RSN_CDE	Text	3
    	SUPP_AVAIL_RSN_CDE	Text	3
    	BRN_ORDER_RSN_CDE	Text	3
    	BRN_SELL_RSN_CDE	Text	3
    	BOS_FULL_NAME	Text	35
    	BOSS_FULL_NAME	Text	35
    	CASE_SIZE	Decimal	16
    	CASE_SIZE_PUOM	Text	6
    	COST_PRICE	Decimal	16
    	VAT_BCODE	Text	1
    	VAT_PCT	Decimal	16
    	SELL_BR_CT	Long Integer	4
    	TLNWKT0_BR_CT	Long Integer	4
    	NonPromoPrice	Decimal	16
    	NonPromoPriceQty	Text	50
    	NonPromoPriceUOM	Text	50
    	NextSellPriceAmt	Decimal	16
    	NextSellPriceDate	Date/Time	8
    	Unit Sales	Double	8
    	Redns	Double	8
    	Scanned Sales	Double	8
    	Offer Costs	Double	8
    	Reg Sales	Decimal	16
    	Sales net of offers and VAT	Double	8
    	Sterl Purch Cost	Decimal	16
    	Contribution 1	Decimal	16
    	ExpWst at Cost	Double	8
    	Contribution 2	Decimal	16
    	Total Bonus	Double	8
    	Contribution 3	Decimal	16
    	DESCRIPTION	Text	255
    	PACK_SIZE	Decimal	16
    	PACK_SIZE_UOM	Text	10
    	PACKAGING_DESC	Text	50
    	ITEM_COUNT	Long Integer	4
    	CUC	Decimal	16
    	STATUS_DESC	Text	10
    	STATUS_CHG_RSN	Text	50
    	COUNTRY_OF_ORIGIN	Text	12
    	SECRET_IND	Text	1
    	PRICE	Decimal	16
    	PRICE_PER	Text	10
    	UNIT_PRICE	Decimal	16
    	UNIT_PRICE_PER	Text	10
    	PREVIOUS_PRICE	Decimal	16
    	OFFER_IND	Text	1
    	OFFER_DESC	Text	255
    	MP_OFFER_IND	Text	1
    	SAVING_AMOUNT	Decimal	16
    	SAVING_PERCENT	Decimal	16
    	COMP_OFFER_TYPE	Text	20
    Code:
    Table: tblQryF_CatDedup	Page: 5
    	MP_TRIGGER	Long Integer	4
    	MP_ITEMS_CHARGED	Long Integer	4
    	MP_OFFER_PRICE	Decimal	16
    	COLLECTION_TYPE	Text	20
    	LEVEL_1	Text	100
    	LEVEL_2	Text	100
    	LEVEL_3	Text	100
    	LEVEL_4	Text	100
    	LEVEL_5	Text	100
    	LEVEL_6	Text	100
    	OwnBrandOrg	Text	1
    	Healthy	Text	1
    	Frozen	Text	1
    	Organic	Text	1
    	Premium	Text	1
    	PRCCHK_DATE	Date/Time	8
    	Table Indexes
    	Name	Number of Fields
    	CompCode	1
    	Fields:
    	CompCode	Ascending
    	CompLineCode	1
    	Fields:
    	CompLineCode	Ascending
    	MatchBasis	1
    	Fields:
    	MatchBasis	Ascending
    	MatchType	1
    	Fields:
    	MatchType	Ascending
    	PRCCHK_DATE	1
    	Fields:
    	PRCCHK_DATE	Ascending
    	Line	1
    	Fields:
    	Line	Ascending
    Last edited by gvee; 06-03-08 at 19:17. Reason: [CODE] tags added for readability

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    lol

    Could you make it any harder for me to see? I am not a machine!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I read a lot of Filtering by... statements in your post. Personally, I'd ditch anything that does Filtering by and go with setting SQL statements to items/recordsources, etc (using parameters). It'll change your speed dramatically.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Iagree with using parameters but he says it works normally sometimes so should be something else, did you check the situation on another computer? it may be your Office problem or your system
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I might expect 'filtering by' to work sometimes and not always since it's more sensitive to data that the filtering doesn't like (such as syntax like: ' , " etc..) If it works sometimes and you're using filtering, I might see if it errors on (or look for syntax in your data) things like: ' or , or " or other of these types of characters. I've always had random error issues with filtering and seeing this kind of syntax somewhere in the data (besides the filtering being 3 times as slow.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    He did say the data was different, so I was thinking something along those lines too.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2008
    Posts
    3
    OK - apologies for the SQL and table defs - but file uploading is blocked through our firewall so copy/paste is the only recourse I have. The SQL query is not complex, not handcrafted just what you can get from using the design view.
    Secondly - I would happily write the SQL needed however the filtering is what is done by the users of the database, so it is not easy to write SQL with parameters to do the filtering for them as I don't know what they might want to filter by. As far as they are concerned they have a datasheet and they want to slice/dice the data as they wish on the day.
    The filtering has been tried on 3 different PCs (however it is perfectly possible that the Office install is flawed on all of them since it is a 'standard' install via SMS.
    The users are not writing sophisticated filters. 'Or' is unlikely to feature - it is usually the case of finding a value they wish to filter by and then using the 'Filter By' option.
    I noticed a strange GUID reference on the relevant table. Maybe I will try recreate that from scratch and copy the data over.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Post above edited - added code tags for readability.
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by StarTrekker
    He did say the data was different, so I was thinking something along those lines too.
    I'm with ya StarTrekker. I never did like using the Filter By or Filter On. Too many problems and toooo slow. I always set the sql statement (with parameters) to the rowsource or recordsource of the form, listbox, combobox, etc..
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yup. The only time I use filtering is on small forms... those with very few records.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •