0
votes

So I am trying to make a query which will compare a monthly time budget to actual time spent. Below is an image of the query in design view. I call this query Monthly Budget Usage(pictured below)

Copy of my Table Relationships

Monthly Budgets (Not Pictured here) is a table with static information which does not change, it stores what each month has budgeted.

Time by Month (pictured below) is a query which takes time entry information and sums up total hours spent by grouping together Clients, and services performed by month. This query is used because we can have multiple time entries for a particular service which span the entire month. But in total they sum up to a particular service budget. (image of query result below with Client and Key hidden as it contains sensitive client data) Time by Month Query

Monthly Budget Usage has a criteria on the month. In a form (not pictured here), I have a combo box in which the user can choose a particular month. Now I added a test record into my data for February (which obviously has not happened yet), but in my form when I enter February as the month I would like to observe budget to actual for, I only see one record (my test client I had entered). In the query options I chose to include all records from monthly budgets yet only the one shows. I was expecting that if there was only one actual time entry, it would show all client time budgets but only one would show as having any time used.

So below is an image of how the query Monthly Budget Usage returns for January (everything is there because we have done all the work we would in January so every budget has some sort of time usage, whether over or under)

January Query Results

But when I sort for February, only one record shows. I would like it to return all Budgets but show 0 usage for those which have no usage, rather than only seeing the one record with usage.

February Query Results

Again, many of these screen captures have unique client information as identifiers that have been hidden.

---------EDITED--------- Monthly Budget

1

1 Answers

1
votes

Your month criteria needs to be applied to the monthly budget table, not the time by month table.

Also, post the related data from your monthly budget table for clarity.