Multiple queries and general issues...

[digitalxni]

Hey guys!

I'm currently in the processes of creating a simple database in order to create quarterly reports based on the data inside it. It's made up of two tables: Carers and Children with a one to many relationship (one carer can have many children). Below are the layouts of the tables.

Carers

Child:

Here are the queries I'm having to run and how I'm doing the ones that I can do. At the moment they are all separate queries but is there any way to condense them all? Otherwise in my report I'll be having several subreports in one master!

Total no. of families registered - Counting the amount of entries in the carer table.
Total no. of children registered - same as above but using the child table
Total no. of families/children using the service this quarter - same as the above two but checking for a 'true' in the AccessedThisQuarter field.
Postcode of each new user this quarter - Selecting the postcode field and display the postcodes where the date registered is between today and 3 months ago.

Number of children accessing this quarter by age: I'm having trouble with this one. I need to show a table with age groups on one side (under 1, 1, 2, 3, 4) and the amount of children on the other side. The problem I'm having is not being able to show the age group if no child falls into it.

The last one I'm having trouble with is similar to the one above but is to do with ethnic origins. I'll have the ethnic origins on the left then two columns: one for children and one for adults (carers and their partners) and I just can't get my head around that one!

Thanks for reading, hopefully someone can come up with some ideas on how to improve my shoddy design!

/xni

 

[Trevor G]

When you want to show age groups even if someone doesn't fall into a group you should use an IIF statement from the expression builder that way it should give you a zero value for the age group something like this

iif([Age Group]Value=0,0,[Age Group])

The second one about ethnic origins should be a simple query from both tables..

The last one I'm having trouble with is similar to the one above but is to do with ethnic origins. I'll have the ethnic origins on the left then two columns: one for children and one for adults (carers and their partners) and I just can't get my head around that one!

Create a query based on both tables your relationship should show in the top, then just draw down the fields you want from both tables. You can set groups if needed by using the autosum icon and then you can use calculations like sum, count etc

 

[digitalxni]

The only problem I can see is that I'm trying to get the ethnic origin from the CarerEthnicOrigin and PartnerEthnicOrigin fields that need to be combined to form the adult group and then I will have to get the ethnic origin from the Child Table.

 

[Trevor G]

Create a temp table if necessary and create an append query to place the list to the temp table.

Would you be able to upload a cut down version of the database and I will take a look for you.

 

[digitalxni]

Yes I will upload this evening as I am currently at work and don't have it with me. Thanks for the suggestions so far!

 

[digitalxni]

Here is the blank database. I've had to add an extra field so I can run a query to count the amount of men that have accessed the services in the quarter.

 

[Trevor G]

I have created a temp table ztblEthincOrigin and also created 2 append queries qryEthnicOriginCarers08042010 and qryEthnicOriginChild08042010 each of them will append the ethnisity to the temp table then I have created a query that will use the temp table and added a group so I can see each ethnisity group and count the records placed in them.

I think if you are holding partner details you will have to do another query to append them as well.

At some stage you would have to create a delete query to empty the temp table before running the appends. You can run everything from a single macro. I have done this as I am sure you have the skills to do this.

Please find attached database.

Kind regards

 

[digitalxni]

Cool! What I really need to try and do though is have the table as follows:

Ethnic Origin | Adults | Children

and have all of the ethnic origins listed even if no one falls into those categories. What you have done is a great start though so thanks!

 

[Trevor G]

Cool! What I really need to try and do though is have the table as follows:

Ethnic Origin | Adults | Children

and have all of the ethnic origins listed even if no one falls into those categories. What you have done is a great start though so thanks!

Ok what I have done is add 2 fields to the ztblEthincOrigin one called adult then the other called child.

Next in the query "qryEthnicOriginCarers08042010" I have added an expression as follows: Adult: IIf([CarerEthnicOrigin]="","","Adult") and selected it to append to the Adult field and all you will have to do is the same in the other query "qryEthnicOriginChild08042010", create the following expression Adult: IIf([EthnicOrigin]="","","Child") and selected to append to the child field.

 

[digitalxni]

I can't seem to get what you said in your last post to work. Why would you be adding the Adult/child fields to the ztblEthincOrigin table? Should they not go in the query? When I put the functions in the append queries they don't alter any data at all

 

[Trevor G]

You need the 2 fields being added to the temp table so you can then use the append queires to use the Expressions so it will identify whether an adult or child. As you have 2 different tables and within one of them(Adults) you are using parent and partner so they can't simply be pulled into one query you have to build the results stage by stage.

You then create your calculations from the ztbl.

As long as you get the IIF statements correct in the append queries and view the results before running them, then you should be in the place where you want to be inorder to count, group etc.

I have done my part, if you can't get this then zip up a copy the database with what you have done and I will check it one last time.

 

[digitalxni]

Might have to send you it later! I'll tell you what I've done so far (pretty much started again and used your ideas). I have 3 append queries (adult, partner and child) and these read the ethnic origins and enter them into a temporary table (child is disabled at the moment because I can't get it to work completely). Then I have a query which groups up the ethnic origins and counts them. So this is what else I have to do: display all the ethnic origins even when there is no one in that group (I'm guessing I'm going to have to enter them manually into a table and use an append query to add numbers into the groups?) and also include the children in the grouping but in a separate column.

Hope that makes sense!