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

I would create some queries to 

[qgrpOrganization]

SELECT  DISTINCT Organization

FROM MasterData;

[qgrpDateOfSubmission]

SELECT DISTINCT DateofSubmission

FROM HealthReportSubmission

[qgrpOrgDates]

SELECT Organization, DateOfSubmission

FROM [qgrpOrganization],[qgrpDateOfSubmission]

You can then JOIN this in your crosstab and set the join to include all of the records from qgrpOrgDates

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Ahmed Hashim <ahmedhashim1@gmail.com>
Sent: Monday, October 19, 2020 3:07 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Ms.Access CrossTab Query Not Inlcude Unmatched Query

 

Hi, 

Sorry for delayed response, please find below SQL of both Queries:

SELECT MasterData.WorkId, MasterData.Organization, MasterData.[2ndLevel], Count(MasterData.WorkId) AS TotalEmp, Count(MasterData.BodyTemperature) AS Submitted, [TotalEmp]-[Submitted] AS NotSubmitted, Round([Submitted]/[TotalEmp]*100,2) & "%" AS [Achievement%], MasterData.DateofSubmission

FROM MasterData

GROUP BY MasterData.WorkId, MasterData.Organization, MasterData.[2ndLevel], MasterData.DateofSubmission;

TRANSFORM IIf(IsError(Round(CInt(Nz(Sum([Submitted]),0))/CInt(Nz(Sum([TotalEmp]),0))*100,2)),0,Round(CInt(Nz(Sum([Submitted]),0))/CInt(Nz(Sum([TotalEmp]),0))*100,2)) & "%" AS Achievement

SELECT HealthReportSubmission.Organization

FROM HealthReportSubmission

GROUP BY HealthReportSubmission.Organization

ORDER BY Format([HealthReportSubmission].[DateofSubmission],"mm/dd/yyyy")

PIVOT Format([HealthReportSubmission].[DateofSubmission],"mm/dd/yyyy");

On Sat, Oct 17, 2020 at 4:52 PM Duane Hookom <duanehookom@hotmail.com> wrote:

The query of unique departments should work. Again you haven't provided any SQL views so we don't know what you tried. The number of records should not be an issue.

Regards,

Duane

---

From: Ahmed Hashim <ahmedhashim1@gmail.com>

 

Hi,

No currently I do not have a separate query/table for selecting unique list of Departments, It is currently the same master which holds employee data alongwith their attendance submissions, yes I know it is a bad design practise but currently I have this only.

I did try to create a unique list of departments using a query and associate this query with the mentioned Crosstab to include all transactions from the master table and only records from this Dept query, but then it apparently hang up the database. Maybe it is because of the number of transactions? total 16186 records in master table.

On Fri, Oct 16, 2020 at 11:15 PM 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:  Ahmed Hashim <ahmedhashim1@gmail.com>

 

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 

_.