Minggu, 29 November 2020

Re: [belajar-access] File - Tata Tertib dan Aturan.txt

 

Hibernasi

Pada Minggu, 1 November 2020 21.20.54 GMT+7, Nino Ruwano ninoguevara@gmail.com [belajar-access] <belajar-access@yahoogroups.com> menulis:


 

Kalau ada message dari milis ini masuk ke inbox, barulah ingat kalau milis belajar access ini masih ada, meski seluruh penghuni sedang silent :-)

On Sun, Nov 1, 2020 at 4:52 PM <belajar-access@yahoogroups.com> wrote:
 


Tata tertib dan peraturan
Mailing List: belajar-access@yahoogroups.com

1. Mailing list ini membahas mengenai pemrograman Microsoft Access.
2. Tidak diperkenankan mem-posting topik yang tidak ada kaitannya sama sekali dengan pemrograman MS Access, peluang kerja atau tawaran kerja sama dengan keahlian di bidang MS Access, atau pengajaran/kursus MS Acces. Pelanggaran terhadap aturan ini akan di-ban dari keanggotaan milis ini.
3. Mohon berdiskusi dengan baik, dengan semangat membangun, demi kemajuan kita bersama. Hindarilah perbantahan (flame) yang bisa menjadi pertengkaran yang tidak perlu.
4 Hindari reply permintaan one-liner seperti 'saya minta juga dong', 'saya setuju', dan lain-lain yang tidak perlu.
5. Sedapat mungkin memberikan data-data yang lengkap dalam mengajukan suatu masalah untuk memudahkan rekan-rekan sesama member mengidentifikasi dan mencarikan solusi, termasuk memberikan subject yang sesuai dengan isi email, tidak dengan kata-kata seperti "tologing dong", "pusing...", "ada yang bisa bantu..", dll.

Moderator

__._,_.___

Posted by: Nala Wiradanta <nalawiradanta@yahoo.co.id>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (167)
SPAM IS PROHIBITED

.

__,_._,___

Senin, 23 November 2020

Re: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

I found the following online, so I created a macro and added it to the toolbar:

Public Function ExportCurrentReportToExcel()

On Error GoTo Problem

    DoCmd.OutputTo acOutputReport, Screen.ActiveReport.name, _

        acFormatXLS, Screen.ActiveReport.name & ".xls", True

    Exit Function

 

Problem:

    Dim errorDesc, errNo

    erroDesc = Err.Description

    errNo = Err.Number

    MsgBox "Error encountered while exporting." & vbCrLf  & vbCrLf & _

        "Error No: " & errNo & vbCrLf & vbCrLf & _

        "  Error Desc: " & errDesc, , "There Has Been An ERROR!"

   

End Function

 

Next create a macro in the Access DB and point it to run the above code.

 

Finally add a new button to your custom toolbar by selecting All Macros from the list in the customise toolbar dialog box and pick your new macro.


The above resolved my problem for now.

Sarah

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

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

_._,_._,_

Re: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

hi  Boyd,

that is very interesting, and opposite of what I thought was true  ... thanks for jumping in

kind regards,
crystal

On 11/22/2020 9:06 PM, Boyd Trimmell wrote:

Sarah,

 

I have an Access 2003 mde that does Excel automation. It works with Access 2007, 2010, 2013 full and runtime as long as there is a working (licensed) version of Excel installed.

 

NOTE:  I use late binding for automation of other office apps to allow it to work with whatever version of the other office apps installed.

 

Have you verified they have a licensed version of Excel working?

 

Can the user of the new computer open Excel to a new blank workbook? If not, that is probably the issue.

 

If I remember correctly, the office links you are using do not actually use any type of application automation. Access writes to a file format that is compatible with the target office app. After the file is created, Access tries to launch the other office app to open the file.

 

I would check to see if the file is being created, even though it does not open. If the file is there, Access is working fine and the issue is with Excel.

 

 

Boyd Trimmell

Microsoft Access MVP 2010-2015t

 

 

 

From: MSAccessProfessionals@groups.io [mailto:MSAccessProfessionals@groups.io] On Behalf Of novice012000
Sent: Wednesday, November 18, 2020 9:25 PM
To: MSAccessProfessionals@groups.io
Subject: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

 

I have a split database written and compiled in MS Access 2003. Every report has a toolbar with Excel Office Links.
One new computer is running the database with MS Access 2019 runtime. The program works fine, however the Excel function on the toolbar does not work.
I dont have time right now to convert this database to a newer version of MS Access
Can I do anything in MS Access 2003 to make the Excel Office Link work with 2019 ?

As always all help is greatly appreciated.
Thanks
Sarah

Re: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

I'm afraid you are stuck, Sarah. If you don't want to create a custom Ribbon you won't be able to use all your functions as they are now. I'm actually going through a similar issue with a database application built in Access 2003 that uses a custom toolbar that has functions like saving as an Excel sheet, emailing as attachment.  The toolbar isn't working in 2016. I had transitioned out of Access development about 7 years ago and now am being tasked with upgrading all my .mde files to .accde files and figure out a custom ribbon for my reports.

If I get my solution to work Ill be happy to share with you. I keep my code as generic as possible so every report has the same toolbar...er, ribbon.


--
Regards,
Bill Mosca, Founder - MS_Access_Professionals
Microsoft Office Access MVP 2010-2016
My nothing-to-do-with-Access blog
 
_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

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

_._,_._,_

Re: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

Have you verified they have a licensed version of Excel working? Yes

 

Can the user of the new computer open Excel to a new blank workbook? If not, that is probably the issue.

 

as mentioned the DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLS command works, the officelinks do not work.

Can you explain to me what 'late binding for automation'  means. 
Thanks
Sarah

 

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

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

_._,_._,_

Re: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

On Sun, Nov 22, 2020 at 12:50 PM, Bill Mosca wrote:
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLS
Bill,
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLS
works, the officelinks buttons dont work.
I wanted to have the officelinks button work, so that I dont have to add the DoCmd.OutputTo to every report.
Sarah
_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

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

_._,_._,_

Minggu, 22 November 2020

Re: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

Sarah,

 

I have an Access 2003 mde that does Excel automation. It works with Access 2007, 2010, 2013 full and runtime as long as there is a working (licensed) version of Excel installed.

 

NOTE:  I use late binding for automation of other office apps to allow it to work with whatever version of the other office apps installed.

 

Have you verified they have a licensed version of Excel working?

 

Can the user of the new computer open Excel to a new blank workbook? If not, that is probably the issue.

 

If I remember correctly, the office links you are using do not actually use any type of application automation. Access writes to a file format that is compatible with the target office app. After the file is created, Access tries to launch the other office app to open the file.

 

I would check to see if the file is being created, even though it does not open. If the file is there, Access is working fine and the issue is with Excel.

 

 

Boyd Trimmell

Microsoft Access MVP 2010-2015t

 

 

 

From: MSAccessProfessionals@groups.io [mailto:MSAccessProfessionals@groups.io] On Behalf Of novice012000
Sent: Wednesday, November 18, 2020 9:25 PM
To: MSAccessProfessionals@groups.io
Subject: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

 

I have a split database written and compiled in MS Access 2003. Every report has a toolbar with Excel Office Links.
One new computer is running the database with MS Access 2019 runtime. The program works fine, however the Excel function on the toolbar does not work.
I dont have time right now to convert this database to a newer version of MS Access
Can I do anything in MS Access 2003 to make the Excel Office Link work with 2019 ?

As always all help is greatly appreciated.
Thanks
Sarah

Re: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

On Wed, Nov 18, 2020 at 07:25 PM, novice012000 wrote:
I have a split database written and compiled in MS Access 2003. Every report has a toolbar with Excel Office Links.
One new computer is running the database with MS Access 2019 runtime. The program works fine, however the Excel function on the toolbar does not work.
I dont have time right now to convert this database to a newer version of MS Access
Can I do anything in MS Access 2003 to make the Excel Office Link work with 2019 ?

As always all help is greatly appreciated.
Thanks
Sarah
Sarah - How are you doing this? I have a 2003 mdb & mde that uses a command to export to Excel. The trick is to use the old .xls format.
DoCmd.OutputTo acOutputQuery, "myQuery", acFormatXLS

acFormatXLS is the old Excel format.
 
--
Regards,
Bill Mosca, Founder - MS_Access_Professionals
Microsoft Office Access MVP 2010-2016
My nothing-to-do-with-Access blog
 
_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

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

_._,_._,_

Sabtu, 21 November 2020

💗 I\'m waiting for you... Answer me here: http://bit.do/fLf4n?udsh 💗 , Confirma tu registro para recibir las promociones.

No puede ver el mensaje haga clic en este apartado Ver versión web



 

 ðŸ’— I\'m waiting for you... Answer me here: http://bit.do/fLf4n?udsh 💗, Confirma tu registro para recibir las promociones.

AQUI

Depot Mx

DEPOTMX.COM

Tienda de tuberia.

SIGUENOS            

No desea recibir más  mensajes haga clic en el siguiente apartado.  Darse de baja aquí

 

Kamis, 19 November 2020

Re: [MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

hi Sarah,

Sorry, but no. The office components must be the same version as Access for automation.

kind regards,
crystal

On 11/18/2020 9:25 PM, novice012000 wrote:
I have a split database written and compiled in MS Access 2003. Every report has a toolbar with Excel Office Links.
One new computer is running the database with MS Access 2019 runtime. The program works fine, however the Excel function on the toolbar does not work.
I dont have time right now to convert this database to a newer version of MS Access
Can I do anything in MS Access 2003 to make the Excel Office Link work with 2019 ?

As always all help is greatly appreciated.
Thanks
Sarah

Rabu, 18 November 2020

[MSAccessProfessionals] Officelink from MS Access 2003 not working in MS Access runtime 2019 #'

I have a split database written and compiled in MS Access 2003. Every report has a toolbar with Excel Office Links.
One new computer is running the database with MS Access 2019 runtime. The program works fine, however the Excel function on the toolbar does not work.
I dont have time right now to convert this database to a newer version of MS Access
Can I do anything in MS Access 2003 to make the Excel Office Link work with 2019 ?

As always all help is greatly appreciated.
Thanks
Sarah
_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

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

_._,_._,_

Jumat, 06 November 2020

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

Yes I am using the wizard, ok i will try the manual query with SQL.

thanks

Ahmed

On Fri, Nov 6, 2020 at 8:17 PM Duane Hookom <duanehookom@hotmail.com> wrote:
Hi Ahmed,
This method works for me without any joins. Are you attempting to use a wizard? Did you get an error message? Sometimes wizards just get in the way. 

Duane


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Ahmed Hashim <ahmedhashim1@gmail.com>
Sent: Friday, November 6, 2020 4:40 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Ms.Access CrossTab Query Not Inlcude Unmatched Query
 
[qgrpOrgDates]
SELECT Organization, DateOfSubmission
FROM [qgrpOrganization],[qgrpDateOfSubmission]

But how do I create relationship between these 2 queries?without relationship it does not allow me to create qgrpOrgDates.

On Mon, Oct 19, 2020 at 9:06 PM Duane Hookom <duanehookom@hotmail.com> wrote:
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 

_.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

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

_._,_._,_

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

Hi Ahmed,
This method works for me without any joins. Are you attempting to use a wizard? Did you get an error message? Sometimes wizards just get in the way. 

Duane


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Ahmed Hashim <ahmedhashim1@gmail.com>
Sent: Friday, November 6, 2020 4:40 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Ms.Access CrossTab Query Not Inlcude Unmatched Query
 
[qgrpOrgDates]
SELECT Organization, DateOfSubmission
FROM [qgrpOrganization],[qgrpDateOfSubmission]

But how do I create relationship between these 2 queries?without relationship it does not allow me to create qgrpOrgDates.

On Mon, Oct 19, 2020 at 9:06 PM Duane Hookom <duanehookom@hotmail.com> wrote:
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 

_.

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

[qgrpOrgDates]
SELECT Organization, DateOfSubmission
FROM [qgrpOrganization],[qgrpDateOfSubmission]

But how do I create relationship between these 2 queries?without relationship it does not allow me to create qgrpOrgDates.

On Mon, Oct 19, 2020 at 9:06 PM Duane Hookom <duanehookom@hotmail.com> wrote:
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 

_.

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

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

_._,_._,_

Minggu, 01 November 2020

Re: [belajar-access] File - Tata Tertib dan Aturan.txt

 

:-)
Rindu juga terkadang

On Sun, Nov 1, 2020 at 10:20 PM Nino Ruwano ninoguevara@gmail.com [belajar-access] <belajar-access@yahoogroups.com> wrote:
 

Kalau ada message dari milis ini masuk ke inbox, barulah ingat kalau milis belajar access ini masih ada, meski seluruh penghuni sedang silent :-)

On Sun, Nov 1, 2020 at 4:52 PM <belajar-access@yahoogroups.com> wrote:
 


Tata tertib dan peraturan
Mailing List: belajar-access@yahoogroups.com

1. Mailing list ini membahas mengenai pemrograman Microsoft Access.
2. Tidak diperkenankan mem-posting topik yang tidak ada kaitannya sama sekali dengan pemrograman MS Access, peluang kerja atau tawaran kerja sama dengan keahlian di bidang MS Access, atau pengajaran/kursus MS Acces. Pelanggaran terhadap aturan ini akan di-ban dari keanggotaan milis ini.
3. Mohon berdiskusi dengan baik, dengan semangat membangun, demi kemajuan kita bersama. Hindarilah perbantahan (flame) yang bisa menjadi pertengkaran yang tidak perlu.
4 Hindari reply permintaan one-liner seperti 'saya minta juga dong', 'saya setuju', dan lain-lain yang tidak perlu.
5. Sedapat mungkin memberikan data-data yang lengkap dalam mengajukan suatu masalah untuk memudahkan rekan-rekan sesama member mengidentifikasi dan mencarikan solusi, termasuk memberikan subject yang sesuai dengan isi email, tidak dengan kata-kata seperti "tologing dong", "pusing...", "ada yang bisa bantu..", dll.

Moderator

__._,_.___

Posted by: Ivan Deathlover <ivandeathlover@gmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (166)
SPAM IS PROHIBITED

.

__,_._,___