How to get a report from queries

[scurmu]

Hi everyone,

I need some help on building my first Access DB. I have started a DB for clients from a job placement agency. I've started doing the DB by following various YouTube videos.
I have one table whit one form where I input all the clients' data (ClientID-PK, FName, LName, National ID number, DOB, Gender, Address (with multiple boxes: street, city, area, etc.), Registration Date, Start Contract, End Contract, Start Activity, End Activity). I made two buttons to fill in two word documents (contract and declaration) with the data from each client record. I'm happy with this so far. I also know how to make a query for a given period of time (between dates).
I don't know how to do a quarterly report with clients that: made a contract with the agency, made employment following the contract with agency, how many males/females, how many by age groups (under 25, 25-29, 30-39, 40-49, 50-55, over 55), how many are still working (taken from the Start Contract and End Contract), how many have left before End Contract.
Do I need to make multiple queries for these? I want to pick the "between dates" myself only once not for every query (if multiple queries needed).
Is it possible to link the data from the report into a word document the same way I've linked the buttons from my table to fill in the contract and declaration?



Many thanks
Sebi

PS: How do I send a private message to another user on this forum?

[ranman256]

Make a report table to hold the qtr data.
in a macro ,put all the append queries that summarize the data and put in this table.
the report pulls from the report tbl.

to send a message ,click the users name in the post, pick send message.

[CJ_London]

A lot to include in one query - basic structure of your query would be something like

SELECT *
FROM myTable
WHERE someDate between [Enter First Date] and [Enter Last Date]

which you could then base other queries on to group by plus sum or count to do what you want - the bits in square brackets are prompts you will get when you run the query, you can change these to reference a form

for ages, depends on how accurate you want to be but simply datediff("yyyy",Date(),DOB). Otherwise age on what? start or end of contract, start or end of activity, Registration date?

Also look at the sql partition function for ages
https://support.office.com/en-us/art...ad=US&fromAR=1


made a contract with the agency - count on start of contract perhaps? or should it be the day they signed the contract? or registration date?
made employment following the contract with agency - would be count of registration date where start contract is not null?
how many males/females - group by gender?, count *
how many by age groups (under 25, 25-29, 30-39, 40-49, 50-55, over 55) - see partition above
how many are still working (taken from the Start Contract and End Contract) - count where end contract date>quarter end date?
how many have left before End Contract. - count where activity end <contract end?

depends how you want to construct your report - for example for gender if you want

Qtr Gender Qty
Q1....M..... 10
Q1....F ..... 10
Q2....M..... 12
Q2....F...... 15

you would need to group by gender

however if you wanted

Qtr Male Female
Q1...10...10
Q2...12...15

then you would have two columns in your query

Male:sum(-gender='M')
Female:sum(-gender='F')

Hope this gives you some ideas on how to proceed

[scurmu]

I've managed to make a query that let me pick my quarter dates.
I couldn't use datediff cause it was giving me wrong ages when BDay was in a month prior present date.
I have used:

Age: DateDiff("yyyy";[DataNasterii];Date())+(Format([DataNasterii];"mmdd")>Format(Date();"mmdd"))

It gives me the right age so far.
Partition function looks ok for equal ranges but mine are not all equal. I don't know how to make Partition function to work for my ranges.
Searching the internet I come across a code that I've tried to replicate it with my data:

I made a table called AgeGroupsT where I defined the groups (under 25, 25-29, 30-39, 40-49, 50-55, over 55).
I made a query called NrClientiTrimestru where I get client's age from BDay from main table.
I try to make another query (AgeQ) to join data from the 2 mentioned above but I get the error:


Here is the code for AgeQ query:

SELECT AgeGroupsT.AgeLow, AgeGroupsT.AgeHigh, Count(NrClientiTrimestruQ.Varsta)FROM AgeGroupsT, NrClientiTrimestruQ
INNER JOIN NrClientiTrimestruQ
ON NrClientiTrimestruQ.Varsta Between AgeGroupsT.AgeLow And  AgeGroupsT.AgeHigh
GROUP BY AgeGroupsT.AgeLow, AgeGroupsT.AgeHigh
ORDER BY AgeGroupsT.AgeLow

Anyone knows why?

[CJ_London]

looks wrong here

FROM AgeGroupsT, NrClientiTrimestruQ
INNER JOIN NrClientiTrimestruQ
ON NrClientiTrimestruQ.Varsta Between AgeGroupsT.AgeLow And AgeGroupsT.AgeHigh


think it should be

FROM AgeGroupsT INNER JOIN NrClientiTrimestruQ
ON NrClientiTrimestruQ.Varsta Between AgeGroupsT.AgeLow And AgeGroupsT.AgeHigh

[scurmu]

I've changed to what you've said Ajax but it gives me the same error and after the error the cursor is blinking right in front of NrClientiTrimestruQ from the Count function.
So I've tried another approach and I made another query and used:

0 to 25: Sum(IIf(DateDiff("yyyy";[DataNasterii];Now())+Int(Format(Now();"mmdd")<Format([DataNasterii];"mmdd"))<25;1;0))
25 to 29: Sum(IIf(DateDiff("yyyy";[DataNasterii];Now())+Int(Format(Now();"mmdd")<Format([DataNasterii];"mmdd")) Between 25 And 29;1;0))
30 to 39: Sum(IIf(DateDiff("yyyy";[DataNasterii];Now())+Int(Format(Now();"mmdd")<Format([DataNasterii];"mmdd")) Between 30 And 39;1;0))
40 to 49: Sum(IIf(DateDiff("yyyy";[DataNasterii];Now())+Int(Format(Now();"mmdd")<Format([DataNasterii];"mmdd")) Between 40 And 49;1;0))
50 to 55: Sum(IIf(DateDiff("yyyy";[DataNasterii];Now())+Int(Format(Now();"mmdd")<Format([DataNasterii];"mmdd")) Between 50 And 55;1;0))
55+: Sum(IIf(DateDiff("yyyy";[DataNasterii];Now())+Int(Format(Now();"mmdd")<Format([DataNasterii];"mmdd"))>55;1;0))

I'll come back with my progress.
Thank you

[CJ_London]

rather than using now, which includes a time element e.g. 06/06/2016 09:53:00, you should use the date() function