Creating a report on field with criteria from OTHER fields

Getting your data onto paper - or the web - Discussing the reports features of Base

Creating a report on field with criteria from OTHER fields

Postby tomwatson » Fri Aug 08, 2008 3:05 pm

I have been trying to generate a report from my database

I have previously been using queries to query the whole database. I have been using the Group and Count options to make reports on my databse. For example to show that I have 100 male clients and 50 female clients. unfortunatly I now need to use the DATE field to only generate reports on those clients who have opened on a certain year and I don't know how to do this. Using the Criterion option in the query doesn't seem to do it unless I am doing it wrong.

Any help would be appreciated.
tomwatson
 
Posts: 6
Joined: Wed May 07, 2008 10:41 am

Re: Creating a report on field with criteria from OTHER fields

Postby Villeroy » Fri Aug 08, 2008 3:56 pm

SELECT ... FROM ... WHERE YEAR("YourDateField")=:Which_Year
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Creating a report on field with criteria from OTHER fields

Postby tomwatson » Mon Aug 11, 2008 10:27 am

I'm actually not sure how to use SQL. I need to create it in query design view.

In SQL my best attemp looks like:

SELECT "HOUSEHOLD SIZE", COUNT( "HOUSEHOLD SIZE" ), "PRIMARY PROBLEMS" FROM "DATABASE 2008-09" WHERE "PRIMARY PROBLEMS" = '11' GROUP BY "HOUSEHOLD SIZE" ORDER BY "HOUSEHOLD SIZE" DESC

but this doesn't work.

Thanks
tomwatson
 
Posts: 6
Joined: Wed May 07, 2008 10:41 am

Re: Creating a report on field with criteria from OTHER fields

Postby Villeroy » Mon Aug 11, 2008 2:00 pm

Code: Select all   Expand viewCollapse view
SELECT "HOUSEHOLD SIZE", COUNT( "HOUSEHOLD SIZE" ) AS "Count", "PRIMARY PROBLEMS"
-- is "DATABASE 2008-09"  the name of a table?
FROM "DATABASE 2008-09" 
WHERE "PRIMARY PROBLEMS" = '11'
-- if any field uses an aggregation (such as COUNT) all other used fields need to be aggregated or grouped:
GROUP BY "HOUSEHOLD SIZE", "PRIMARY PROBLEMS"
ORDER BY "HOUSEHOLD SIZE" DESC

I suspect that you have different tables for every month, which is not how databases work in general. Put all equally structured data into one large table and try a query like :
Code: Select all   Expand viewCollapse view
SELECT "HOUSEHOLD SIZE", COUNT( "HOUSEHOLD SIZE" ) AS "Count", "PRIMARY PROBLEMS"
FROM "Households" 
WHERE "PRIMARY PROBLEMS" = '11' AND Year("Date")=2008 AND Month("Date")=9
-- if any field uses an aggregation (such as COUNT) all other used fields need to be aggregated or grouped:
GROUP BY "HOUSEHOLD SIZE", "PRIMARY PROBLEMS"
ORDER BY "HOUSEHOLD SIZE" DESC

If the WHERE clause uses parameters, you will be prompted for the missing arguments (so called parameter query):
Code: Select all   Expand viewCollapse view
...
WHERE "PRIMARY PROBLEMS" =:Problem_ID AND Year("Date")=:Year AND Month("Date")=:Month_Number
...

unquoted argument names with leading colon, no spaces allowed.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest