Re: [MSAccessProfessionals] Ms.Access CrossTab Query Not Inlcude Unmatched Query

You need to create a table which holds all dates in a range of years.

Create a Cartesian query with this table and the table with dept field so that the "missing" info can be derived. Use this query to your crosstab together with any related table.

On Sat, Oct 17, 2020, 2:15 AM Duane Hookom <duanehookom@hotmail.com> wrote:

Hi Ahmed,

I expect you could assure the new department would display if you added the Organization/Department table to the query using a join that includes all of the organizations/departments. We don't know if you have such a table and can't see your SQL. 

Regards,

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Ahmed Hashim <ahmedhashim1@gmail.com>
Sent: Friday, October 16, 2020 1:52 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Ms.Access CrossTab Query Not Inlcude Unmatched Query

 

Dear All Professionals,

I am using Ms.Access 2016 version windows 10, having database with following:

Table: Employee Master Data with Attendance submission daily entries( the field Organization is basically Department), the Count field is the indicator for submission with 1, and not submit as 0.



Query: data group by Departments, Total Number Employees, Attendance submission and Non-Submissions(these are calculated in Query)


Now based on above query I created a Crosstab query which shows date as Column headings, and Departments as rows, with Achievements as Value resulting in follows:

Now the issue is, there is a new Department created with some employees hired in mid of month, lets say 15th October 2020, there data is not showing in above crosstab, the whole department is not showing, my understanding is that because there is data not available from 1st Oct till 14th October, the Crosstab query skips this new Department altogether. Even data from 15th October onwards is not showing. 

The workaround so far is, I have to manually add dummy data from 1st Oct till 14th Oct with non-submissions, only after that Crosstab query is able to show the records. 

is there any other solution to make this automate? Please advice/suggest any changes.

Best regards

Ahmed 

_._,_._,_

---

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#115776) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_