Whats the point of queries...

[jonathanchye]

Perhaps I am wrong but I just don't get queries. I mean, yes they are brilliant but no matter how hard I try I can only seem to get a query to return a single criteria.

This is not really good when you need to generate report and also charts based on selected table values.

For example, I can generate an Orders query sorted b y month spanning across 3 years. I can easily generate a report and even a chart based on this query which is brilliant.

However, when I want to add an additional criteria, such as within each year also show the status of the Orders (Live,Closed) and I can't seem to find a way to do this! At least not a straightforward or easy way.

I hope someone could say that I am wrong here as I have tried so many methods but can't seem to get this to work.

I can create a custom form using Textboxes with DCount method to get the values I want but its nigh impossible to generate a chart (automatically) based on textboxes generated using this method.

 

[CBrighton]

It all depends on whether your database has been built to be a relational database with tables linking together, etc. Assuming you are talking about multiple tables of course.

You can use any number of criteria in a query, I regularly have multiple criteria on my reports. In fact I have multiple criteria over multiple tables.

It's all about correctly linking your tables.

 

[jonathanchye]

It all depends on whether your database has been built to be a relational database with tables linking together, etc. Assuming you are talking about multiple tables of course.

You can use any number of criteria in a query, I regularly have multiple criteria on my reports. In fact I have multiple criteria over multiple tables.

It's all about correctly linking your tables.

Well all the data is in the same table. I just have to apply conditional formatting to some of the fields. For example I have one field called orderStatus which can be live or close. I just can't find a way to easily generate a report which would format the data as I want it.

I am using queries to do that at the moment but queries only allow me to set 1 criteria. (in this case only showing orderStatus live or closed)

 

[DCrake]

I feel the frustration is bourne from not being able to have more than one value column in a crosstab query. You may be better off exporting the data to Excel and using a pivot table.

 

[jonathanchye]

I feel the frustration is bourne from not being able to have more than one value column in a crosstab query. You may be better off exporting the data to Excel and using a pivot table.

That will defeat the purpose of "upgrading" to Access then... we are currently using Excel for everything.

I guess the only problem lies with reports and charts. I can still generate reports by using Dcount method (although loads slower compared to basing on queries) but there's no way to generate charts as it won't accept Dcount fields.

I am secretly hoping I am wrong here but from I am slowing thinking this might be one of the limitation of using Access...

Why can't they just add the ability to apply Conditional formatting in PivotChart view

 

[CBrighton]

I must admit, I avoid cross-tab queries whenever possible.

Which started a loop... I avoid them because I don't do them often and don't have a high level of knowledge on them, so I will never learn!

 

[boblarson]

I am using queries to do that at the moment but queries only allow me to set 1 criteria. (in this case only showing orderStatus live or closed)

Then you still have a way to go before you can truly judge things. Queries are ESSENTIAL for use with a database because how else would you get the data you need? If you can't get more than one criteria to work then you are not doing it correctly and we need to identify what you are doing and then let you know what you are doing wrong.

And as mentioned already, in order to get the most out of a relational database, you need to have a normalized table structure. So, if it isn't, then it is going to be a real pain.

But queries are something that you just need to get the hang of. In fact, you might not be aware of this, but when you "open" a table in Access to look at it, you really are not opening the table itself. You are really opening a query (albeit a system one).

So, hang in there. It does get easier with time and experience.

 

[jonathanchye]

Then you still have a way to go before you can truly judge things. Queries are ESSENTIAL for use with a database because how else would you get the data you need? If you can't get more than one criteria to work then you are not doing it correctly and we need to identify what you are doing and then let you know what you are doing wrong.

And as mentioned already, in order to get the most out of a relational database, you need to have a normalized table structure. So, if it isn't, then it is going to be a real pain.

But queries are something that you just need to get the hang of. In fact, you might not be aware of this, but when you "open" a table in Access to look at it, you really are not opening the table itself. You are really opening a query (albeit a system one).

So, hang in there. It does get easier with time and experience.

Yeah, I guess I was just a bit frustrated. I can so far manage to display everything I want but only in PivotTable View...

As for normalising I believe that's not the problem. All my data is from one single table. Infact its is mostly from ONE field. That field however has 3 possible values. All I want to do is group them by first the year and then Count the number of entries in those 3 categories per month.

There must be a way but definitely not an easy way. I can now get the chart generated as what I did was save the query (after designing it in PivotTable view to make sure every info is there) as a Form. I then use the PivotChart view to create the required chart and then save the form so users can only view PivotChart view. I then insert the form in the report as a subreport. Not the best way but at least it works.

I wish I can do the same for PivotTable but I just can't. I can insert the form as PivotTable view but it just looks wrong.

Too bad I can't format the report to look the same way as the PivotTable...

 

[boblarson]

Yeah, I guess I was just a bit frustrated. I can so far manage to display everything I want but only in PivotTable View...

As for normalising I believe that's not the problem. All my data is from one single table. Infact its is mostly from ONE field. That field however has 3 possible values. All I want to do is group them by first the year and then Count the number of entries in those 3 categories per month.

There must be a way but definitely not an easy way. I can now get the chart generated as what I did was save the query (after designing it in PivotTable view to make sure every info is there) as a Form. I then use the PivotChart view to create the required chart and then save the form so users can only view PivotChart view. I then insert the form in the report as a subreport. Not the best way but at least it works.

I wish I can do the same for PivotTable but I just can't. I can insert the form as PivotTable view but it just looks wrong.

Too bad I can't format the report to look the same way as the PivotTable...

I would love to see what you have and see if I can do anything about it. But I would have to, again, wait until I get to my Access 2010 installation.

 

[jonathanchye]

Thanks Bob. I have converted my DB to support Access 2007 now by removing all the Calculated fields in my table (hence why running into all the problems now )

Have a read at this thread if you have the time. Explains my frustration better :

http://www.access-programmers.co.uk/forums/showthread.php?t=207844

 

[boblarson]

Can you upload the current copy?

 

[jonathanchye]

Can you upload the current copy?

I am afraid it is filled with live data at the moment mate. Did you have a read at the thread I linked? Is it possible to create a report based on the format I've posted?

 

[boblarson]

I am afraid it is filled with live data at the moment mate.

Well, when you get a chance it would probably be a world of help to me (and perhaps others) in getting your questions answered to your satisfaction. I'm much more "hands on" and like to play around with things to get them where they need to be rather than trying to think of them off the top of my head and suggest each little step. So, your choice, but if you can do it, I think we might be able to solve the problems a little, or much, faster.

 

[jonathanchye]

I will see what I can do. I have already formatted it correctly in PivotTable View but just need to get it working with Reports...

I could upload only the query object but I don't think that would be any use without data?

 

[Banana]

FWIW, addressing only the point of pivottable: Access allows you to create a PivotTable Form so it's not strictly required that you have to leave Access if you want to have pivottable view. However, it's non-updatable but when I had a complex display requirement, I did indeed use a PivotTable form and implemented a means to update the recordset interactively.

Of course, if the data isn't normalized, no views or queries (or indeed anything else) will be able to help you make it easy as it should be.

 

[jonathanchye]

FWIW, addressing only the point of pivottable: Access allows you to create a PivotTable Form so it's not strictly required that you have to leave Access if you want to have pivottable view. However, it's non-updatable but when I had a complex display requirement, I did indeed use a PivotTable form and implemented a means to update the recordset interactively.

Of course, if the data isn't normalized, no views or queries (or indeed anything else) will be able to help you make it easy as it should be.

Thanks for the input. I believe the data is pretty normalised already. Although the table contains a fair number of fields the query only takes 3 fields into consideration. However I need to format the data so it is grouped correctly. Only way I can do that is in PivotTable at the moment and yes I agree that the data does not update automatically. Another way for me to present data is to maybe format the report into 2 columns etc but I will have a look into that. It's a shame the report restricts Column headings to only 1 field...

 

[Simon_MT]

Not knowing exactly what you are trying to acheive, any Grouping or Aggregate Function will render the RecordSource to be non Updateable but, you could try creating separate Query for each Column and join these queries together to get multiple columns. Whilst this may not be the solution but you know what you want to acheive, it is just that Access is not being compliant. Whose boss?

Simon

 

[jonathanchye]

Not knowing exactly what you are trying to acheive, any Grouping or Aggregate Function will render the RecordSource to be non Updateable but, you could try creating separate Query for each Column and join these queries together to get multiple columns. Whilst this may not be the solution but you know what you want to acheive, it is just that Access is not being compliant. Whose boss?

Simon

I've been trying to do this all the while but can't do it..

Basically there's 2 basic must have fields : Month and year

Easy as that.

So I would have a query which looks like this :

------ Year
Month (Data)

The (Data) howoever is further divided into 3 fields : Live, Closed and Won.

So I would have something like this :
----- Year
Month (CountOfLive, CountOfClosed, CountOfWon)

I can do this for one year but lets say if I want to add more years in my report it will be formatted like this :

----Year1
Months (Counts...)
----Year2
Months (Counts...)

instead of :

-----------Year1-------------- Year2---------------Year3
Months (Live,Closed,Won)--(L,C,W)-------------(L,C,W)


p/s: Bob - still haven't heard back from you in regards to the files I've sent earlier? Still working on it?

 

[Simon_MT]

Lets try a little guesswork - lets look at months first:

Assuming Grouping forgive my syntax!

Month Group By

Thi is what you need to acheive:

iif([Live] = True,Count([ID])) as CountofLive
iif([Closed] = True,Count([ID])) as CountofClosed
iif([Won] = True,Count([ID])) as CountofWon

I'm guessing but you basically need to flat the Months (and Years) with a Group By and create columns for the Count of Live, Closed and Won, not that I'm telling you anything new!

Start without as a straight select query and then add totals and your Group By.

Simon

 

[jonathanchye]

Lets try a little guesswork - lets look at months first:

Assuming Grouping forgive my syntax!

Month Group By

Thi is what you need to acheive:

iif([Live] = True,Count([ID])) as CountofLive
iif([Closed] = True,Count([ID])) as CountofClosed
iif([Won] = True,Count([ID])) as CountofWon

I'm guessing but you basically need to flat the Months (and Years) with a Group By and create columns for the Count of Live, Closed and Won, not that I'm telling you anything new!

Start without as a straight select query and then add totals and your Group By.

Simon

Almost there but [Live] is actually a single field in my table.

Below are the fields of the table :

[DateReceived] (Date Enquiry Received formated as British Date dd/mm/yyyy)
[Live] - not a good name I know will change this soon (Values : Live, Closed, Won)
[EnqNo] (Enquiry no, this is unique per enquiry)

This is all the fields in the table.

I want to create a report based on this table with the following format:

(Please ignore the ----) # - number of records ie Count [Live] ='Live' etc...

------------------- 2009-------------------------2010----------------2011
-----------{Live}{Closed}{Won}----{Live}{Closed}{Won}----{Live}etc,....
January----#------#------#----------#------#-------#--------------#
Feb--------#------#------#---------#-------#-------#