Group by Query

[ConnorGiles]

To me this would suggest that one of the field names in your query that your chart is based off has an invalid field name.

I would love to see the sample database Alex, But you would need to compile and repair and save in 2007 format for me please.

 

[AlexN]

To me this would suggest that one of the field names in your query that your chart is based off has an invalid field name.

I would love to see the sample database Alex, But you would need to compile and repair and save in 2007 format for me please.

Here it comes
Thank you!

 

[ConnorGiles]

I'm not giving the answer No-one ever learns this way but :

This is an error that is usually caused by the chart/query trying to find a field name which isn't actually in your table.

The error basically is giving you a clue to this, somewhere inside your code you have incorrectly related to a invalid field name.

Hint,Hint - I've had a look and your Cross tab keeps telling you something to do with :

[Forms!ProductFilter!ProductCombo]

 

[AlexN]

I'm not giving the answer No-one ever learns this way but :

This is an error that is usually caused by the chart/query trying to find a field name which isn't actually in your table.

The error basically is giving you a clue to this, somewhere inside your code you have incorrectly related to a invalid field name.

Hint,Hint - I've had a look and your Cross tab keeps telling you something to do with :

[Forms!ProductFilter!ProductCombo]

Still nothing doing. I've tried everything, even illogical things, but nothing changed. Something wrong is happening when chart form is about to load. Crosstab query works fine. Transforming it into a chart is a disaster. Maybe I'm too stupid but your hint didn't help.

Thank you

 

[ConnorGiles]

Okay, the Crosstab may be working fine - but I struggle to understand why you have the crosstab relating to another query

A crosstab is a query in itself. Try relating the crosstab straight to the table rather than the other query.

Then we shall see where we are.

Also amend the table to the new crosstab also.

 

[AlexN]

Okay, the Crosstab may be working fine - but I struggle to understand why you have the crosstab relating to another query

A crosstab is a query in itself. Try relating the crosstab straight to the table rather than the other query.

Then we shall see where we are.

Also amend the table to the new crosstab also.

Well, as you probably have noticed, field Product in tblSales, is a lookup field taking values from tblProducts, and holding ProductID value only showing Product name.
Thus, when it comes to the crosstab query, column headings have the value of ProductID (1,2,3,etc), That's a bit annoying and providing no information. That's why I first created qrySalesProducts.

 

[ConnorGiles]

Try remaking your Chart.

I have attached my version of your chart - Look at it in Datasheet view. (it doesn't ask for a parameter just does its job.)

I just used your qrySales - not the crosstab - and it seemed to work fine .

 

[AlexN]

Try remaking your Chart.

I have attached my version of your chart - Look at it in Datasheet view. (it doesn't ask for a parameter just does its job.)

I just used your qrySales - not the crosstab - and it seemed to work fine .

Works fine even with parameters. Thank you so much. So it seems crosstab query doesn't accept parameters and in this case it is not needed at all.

Thanks again I'm grateful!

 

[ConnorGiles]

Anytime mate,

It does accept them just obviously disliked your specific ones

Have a Great Christmas Alex.

Kind Regards

Connor

 

[AlexN]

Merry Christmas to you too!!

 

[Brianwarnock]

I know that this is solved but reading through I see that you use table lookups, you will find that they give heartache aswell as headache, to the experts on here they are a great big no no.

I've never used them but have seen problems caused by their use.

Brian

 

[AlexN]

I know that this is solved but reading through I see that you use table lookups, you will find that they give heartache aswell as headache, to the experts on here they are a great big no no.

I've never used them but have seen problems caused by their use.

Brian

Thanks for the remark. But, what would you suggest instead ? Every book I've read uses them.

 

[CJ_London]

Table lookups are a matter of personal choice, but they are generally not recommended because they cause issues with development - basically you forget they are lookups, see one value (usually text) but actually it is another - usually a number.

Whenever you run a query, for whatever purpose, then the query associated with the lookup field also has to run with an impact on performance.

It also means that whenever you have a form or report the control associated with the field has to be a combo box, otherwise you will see the underlying value.

OK, small dataset, simple structure, use a lookup if you want but don't develop the habit. Large dataset, complex structure, chance of upsizing to SQL server or whatever, don't use lookups

The only time I use them is for a small, never to be changed value list such as start/end, morning/afternoon/evening or time breaks.

What to use instead? Nothing - just define your field as normal and create a combo box when required (basically when you want to select a customer)- the lookup sql will be the same as you would use in your table. And if you need to see the customer name in a form or report just link in your source query

 

[AlexN]

Of course you have your reasons for saying so, and I have no right to doubt them. I'm not an experienced Access user, I only began designing small databases a few years ago as a hobby, and all books I've read recommended the use of lookup fields in tables. Even Access itself recommends it when you analyze a table for best performance.

I’m willing to follow you advice of course, since it is obvious that you have tons of experience but having learn it this way I just can’t interpret it.

In fact, I would avoid using lookup fields, only in a small database, for fields that change rarely, and could take up values out of a small variety.

On the other hand, when you have huge tables and fields with values frequently recurring and come from a huge variety, how would you recommend to enter data? Typing the same thing over and over again isn’t comfortable for no one and could easily lead to mistakes

Suppose he have a database that tracks down everyday purchases and other expenses of a housekeeper (of course this isn't a huge database but it could develop to one). Can you imagine user typing over and over again, fruits, vegetables, meat, milk, pasta, and all the huge variety of things on which he spends money everyday? And if this database is used by two different people (pretty common), the second one would have to go back some hundreds of records to see how the first one registered ravioli (was it pasta or was it ravioli).

I have no intense to doubt you or anyone else of course. It’s just that at this time I’m most likely in the user’s chair than in the developer’s and I’m thinking like one. Want to learn though.

Thank you.

 

[CJ_London]

Alex - your assumption appears to be that a user will enter data directly into a table, whereas the expectation is they will enter data in a form. Either that or you are perhaps assuming that unless you define a field as a lookup you cannot use a combobox on a form for that field.

So my answer to this

On the other hand, when you have huge tables and fields with values frequently recurring and come from a huge variety, how would you recommend to enter data?

is in a form, and if you have this huge variety, perhaps the user wants to limit the list in some way which will vary depending on circumstances - perhaps one time the user only wants a list of what was purchased in the last 12 months, another time wants to restrict the list to varieties of apples. You must of seen the hundreds of threads on this forum about cascading combo's etc., none of which are solved by having a lookup field in a table.

Can you imagine user typing over and over again, fruits, vegetables, meat, milk, pasta, and all the huge variety of things on which he spends money everyday?

Why would they be typing descriptions?

But lets say your shopper does type the item descriptions but you want to save them some work, so you make your field a lookup to look up what is already in the list to take advantage of the typeahead functionality - so the rowsource is something like

SELECT DISTINCT Items FROM myTable Order By Items

In the table the user enters an item, then while on the same list a bit later wants to enter the same item again - it isn't there - why? because the list hasn't been updated. The only way to do it in a table is by closing and reopening the table or by the user hitting F9, both inconvenient for the user

However in a form, you can use VBA or a macro to refresh the list in the control afterupdate event - the user is unaware ad not inconvenienced.

OK - you can say it is the same code either way (table or form)- but my point is it does not work effectively in a table, only in a form.

 

[CJ_London]

I meant to post this link as well!

http://access.mvps.org/access/lookupfields.htm

 

[AlexN]

….you are perhaps assuming that unless you define a field as a lookup you cannot use a combobox on a form for that field.

You bet I was! This is how I’ve learned it. No one of the tens of books I‘ve read suggests any other way. And yes, cascading combo’s especially in continuous subforms are a pain in the ass every time you want to use them but, recently found a way to solve this… http://www.access-programmers.co.uk/forums/showthread.php?t=271103

SELECT DISTINCT Items FROM myTable Order By Items

Never crossed my mind. I said I’m not experienced. Thank you.
Needless to say I’ve always used forms with comboboxes to enter data in a table with lookup fields, but didn’t know any other way to do it. For values that didn’t exist in the list I used a ‘Not in list’ event procedure.

Well, I’ve read this http://access.mvps.org/access/lookupfields.htm a couple of days ago and by some search found a dozen of counter theories (don’t remember links) but, in any case, it’s something worth knowing.


Thank you for taking the time explaining, and showing alternative options. Perhaps I should redesign some tables (that could save me a lot of complexity) and now maybe I could find a solution to the problem http://www.access-programmers.co.uk/forums/showthread.php?t=272420 I’m struggling for days with.

Thanks again, you’ve been enlightening.


Nick

 

[Brianwarnock]

Thank you CJ. For picking this up.

Brian

 

[AlexN]

Thank you CJ. For picking this up.

Brian

Thank you Brian for bringing this up!

 

[namliam]

Just because "one voice is no voice" I want to backup CJ's claims.
Not only are lookups in your tables cause for performance issues, they also cause problems when you run queries and such. A lot of people eventually run into these problem.

Just because M$ wants you to use their "new shinny thingy" doesnt mean that there are no other (or even better) ways.
For this same reason you have a naming convention in place, using tblSales as a name instead of simply Sales or worse "My table of something important"