Selasa, 31 Oktober 2017

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

 


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

__._,_.___
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (128)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
SPAM IS PROHIBITED

.

__,_._,___
Diposting oleh di Tidak ada komentar:

[AccessDevelopers] File - Monthly_Notices.txt

 


Monthly notices:

Hi Kids!:

Don't forget to check out our "Links" section at the website for helpful sites. Also take a peek at books that others have found worthwhile in our books database under the 'Database' link of the main AccessDevelopers page. Feel free to add any books or links that you have found useful.

__._,_.___
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (160)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

Please zip all files prior to uploading to Files section.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] FIELD JOIN IN QUERY

 

It's actually a single query using CTEs (Common Table Expressions). I really like using them since you have great flexibility and functionality.

Duane

Get Outlook for iOS
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, October 31, 2017 6:29:09 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


Hi Duanne,

if i get it right there are two queries and not three ?

Thanks.

Ade


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, 31 October 2017, 22:35
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,
This is how I would create the query in SQL Server

With 
AdeQuni1 As
(
SELECT Record, Field1
FROM AdeTable1
UNION ALL
SELECT Record, Field2
FROM adeTable1
UNION ALL
SELECT Record, Field3
FROM adeTable1
UNION ALL
SELECT Record, Field4
FROM adeTable1
UNION ALL SELECT Record, Field5
FROM adeTable1)
,AdeQuni2 AS
(SELECT Record, Field1
FROM AdeTable2
UNION ALL
SELECT Record, Field2
FROM adeTable2
UNION ALL SELECT Record, Field3
FROM adeTable2)

SELECT AdeQuni1.Record, AdeTable1.Field1, AdeTable1.Field2, AdeTable1.Field3, AdeTable1.Field4, AdeTable1.Field5
FROM AdeTable1 INNER JOIN (AdeQuni2 INNER JOIN AdeQuni1 ON AdeQuni2.Field1 = AdeQuni1.Field1) ON AdeTable1.Record = AdeQuni1.Record
GROUP BY AdeQuni2.Record, AdeQuni1.Record, AdeTable1.Field1, AdeTable1.Field2, AdeTable1.Field3, AdeTable1.Field4, AdeTable1.Field5
HAVING (((Count(AdeQuni1.Field1))=3));

Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, October 31, 2017 5:15 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


I believe the syntax would be the same or very close. Some times the joins are a bit different. Give it a try.

Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, October 31, 2017 4:07 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


A little over 2 million with results of about 455k expected. I tried exporting to text but stil the same results. Will the syntax be exactly as in MSACCESS ? If so that will be a good idea

Ade


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, 31 October 2017, 20:28
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,

How many records are you querying? My only other trick would be to use SQL Server. There is a free version.

Regards,
Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, October 31, 2017 2:21 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


Thanks Duane,

it worked well for the sample data.  when applied to the real data i am evaluating, which is large, the query does not run but it says there is not space in temp or size greater than 2GB.

Any trick to go around this ?

Thanks.

Ade


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, 30 October 2017, 19:31
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,
If I understand, create two union queries you need to have a unique value in each record. I used the Record column/field.

AdeQuni1
SELECT Record, Field1
FROM AdeTable1
UNION ALL
SELECT Record, Field2
FROM adeTable1
UNION ALL
SELECT Record, Field3
FROM adeTable1
UNION ALL
SELECT Record, Field4
FROM adeTable1
UNION ALL SELECT Record, Field5
FROM adeTable1;

and 
AdeQuni2
SELECT Record, Field1
FROM AdeTable2
UNION ALL
SELECT Record, Field2
FROM adeTable2
UNION ALL
SELECT Record, Field3
FROM adeTable2;

Then create a third query with SQL of
SELECT AdeQuni1.Record, AdeTable1.Field1, AdeTable1.Field2, AdeTable1.Field3, AdeTable1.Field4, AdeTable1.Field5
FROM AdeTable1 INNER JOIN (AdeQuni2 INNER JOIN AdeQuni1 ON AdeQuni2.Field1 = AdeQuni1.Field1) ON AdeTable1.Record = AdeQuni1.Record
GROUP BY AdeQuni2.Record, AdeQuni1.Record, AdeTable1.Field1, AdeTable1.Field2, AdeTable1.Field3, AdeTable1.Field4, AdeTable1.Field5
HAVING (((Count(AdeQuni1.Field1))=3));

Duane Hookom



From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, October 30, 2017 11:25 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


Please see below.

TABLE1
Record Field1 Field2 Field3 Field4 Field5
1 1 29 40 41 48
2 7 18 19 32 48
3 6 24 32 48 50
4 1 9 15 19 25
5 4 21 34 36 37
6 23 29 37 45 50
7 13 17 19 26 36
8 4 17 23 27 30
9 9 11 13 27 33
10 3 16 23 32 39
TABLE2
Record Field1 Field2 Field3
1 2 23 39
2 3 16 32
3 3 18 23
4 3 39 44
5 11 32 39
OUTCOME
Record Field1 Field2 Field3 Field4 Field5
10 3 16 23 32 39


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, 30 October 2017, 15:31
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,

Again, can you provide some sample data and desired output? I think you would need to create union queries to normalize your table but I can't tell.

Regards,
Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, October 30, 2017 9:56 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


Thanks Duanne,

i am trying to solve a problem with some combinatorics element. There is no unique ID in the first table that equates to another ID in the second.

While the first table contains all the possibilities, my desired outcome is only those lines or records in the first table that have a record from the second table as a subset regardless of the order.

The challenge is how to join both tables to achieve this result when i run a query


Ade


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, 30 October 2017, 14:38
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,

It seems your tables aren't normalized correctly. Can you provide more information about what types of values you are storing? I am picturing something like storing EmployeeIDs  (40 different employees) in any of the fields with headings/field names that equate to positions or roles. If this is somewhat like you system, rather than 5 fields, there should be 5 records.

How would you join? Can you provide some sample records and desired output?

Regards,
Duane Hookom


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, October 30, 2017 6:02 AM
To: ms_access_professionals@yahoogroups.com
Subject: [MS_AccessPros] FIELD JOIN IN QUERY
 


I am looking for help to create a join between two tables. Fields 1-5 in the first table can take on about 40 different unique values, while Fields 1-3 in the second table can also take on the same number of unique values. Fields 1 to 3 can each equate to any of the values in Fields 1-5 in the 1st table.

Thanks.

Ade

TABLE1
FIELD1 FIELD2 FIELD3 FIELD4 FIELD5
TABLE2
FIELD1 FIELD2 FIELD3
























__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (14)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:

Re: [MS_AccessPros] FIELD JOIN IN QUERY

 

Hi Duanne,

if i get it right there are two queries and not three ?

Thanks.

Ade


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, 31 October 2017, 22:35
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,
This is how I would create the query in SQL Server

With 
AdeQuni1 As
(
SELECT Record, Field1
FROM AdeTable1
UNION ALL
SELECT Record, Field2
FROM adeTable1
UNION ALL
SELECT Record, Field3
FROM adeTable1
UNION ALL
SELECT Record, Field4
FROM adeTable1
UNION ALL SELECT Record, Field5
FROM adeTable1)
,AdeQuni2 AS
(SELECT Record, Field1
FROM AdeTable2
UNION ALL
SELECT Record, Field2
FROM adeTable2
UNION ALL SELECT Record, Field3
FROM adeTable2)

SELECT AdeQuni1.Record, AdeTable1.Field1, AdeTable1.Field2, AdeTable1.Field3, AdeTable1.Field4, AdeTable1.Field5
FROM AdeTable1 INNER JOIN (AdeQuni2 INNER JOIN AdeQuni1 ON AdeQuni2.Field1 = AdeQuni1.Field1) ON AdeTable1.Record = AdeQuni1.Record
GROUP BY AdeQuni2.Record, AdeQuni1.Record, AdeTable1.Field1, AdeTable1.Field2, AdeTable1.Field3, AdeTable1.Field4, AdeTable1.Field5
HAVING (((Count(AdeQuni1.Field1))=3));

Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, October 31, 2017 5:15 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


I believe the syntax would be the same or very close. Some times the joins are a bit different. Give it a try.

Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, October 31, 2017 4:07 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


A little over 2 million with results of about 455k expected. I tried exporting to text but stil the same results. Will the syntax be exactly as in MSACCESS ? If so that will be a good idea

Ade


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, 31 October 2017, 20:28
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,

How many records are you querying? My only other trick would be to use SQL Server. There is a free version.

Regards,
Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, October 31, 2017 2:21 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


Thanks Duane,

it worked well for the sample data.  when applied to the real data i am evaluating, which is large, the query does not run but it says there is not space in temp or size greater than 2GB.

Any trick to go around this ?

Thanks.

Ade


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, 30 October 2017, 19:31
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,
If I understand, create two union queries you need to have a unique value in each record. I used the Record column/field.

AdeQuni1
SELECT Record, Field1
FROM AdeTable1
UNION ALL
SELECT Record, Field2
FROM adeTable1
UNION ALL
SELECT Record, Field3
FROM adeTable1
UNION ALL
SELECT Record, Field4
FROM adeTable1
UNION ALL SELECT Record, Field5
FROM adeTable1;

and 
AdeQuni2
SELECT Record, Field1
FROM AdeTable2
UNION ALL
SELECT Record, Field2
FROM adeTable2
UNION ALL
SELECT Record, Field3
FROM adeTable2;

Then create a third query with SQL of
SELECT AdeQuni1.Record, AdeTable1.Field1, AdeTable1.Field2, AdeTable1.Field3, AdeTable1.Field4, AdeTable1.Field5
FROM AdeTable1 INNER JOIN (AdeQuni2 INNER JOIN AdeQuni1 ON AdeQuni2.Field1 = AdeQuni1.Field1) ON AdeTable1.Record = AdeQuni1.Record
GROUP BY AdeQuni2.Record, AdeQuni1.Record, AdeTable1.Field1, AdeTable1.Field2, AdeTable1.Field3, AdeTable1.Field4, AdeTable1.Field5
HAVING (((Count(AdeQuni1.Field1))=3));

Duane Hookom



From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, October 30, 2017 11:25 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


Please see below.

TABLE1
Record Field1 Field2 Field3 Field4 Field5
1 1 29 40 41 48
2 7 18 19 32 48
3 6 24 32 48 50
4 1 9 15 19 25
5 4 21 34 36 37
6 23 29 37 45 50
7 13 17 19 26 36
8 4 17 23 27 30
9 9 11 13 27 33
10 3 16 23 32 39
TABLE2
Record Field1 Field2 Field3
1 2 23 39
2 3 16 32
3 3 18 23
4 3 39 44
5 11 32 39
OUTCOME
Record Field1 Field2 Field3 Field4 Field5
10 3 16 23 32 39


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, 30 October 2017, 15:31
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,

Again, can you provide some sample data and desired output? I think you would need to create union queries to normalize your table but I can't tell.

Regards,
Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, October 30, 2017 9:56 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY
 


Thanks Duanne,

i am trying to solve a problem with some combinatorics element. There is no unique ID in the first table that equates to another ID in the second.

While the first table contains all the possibilities, my desired outcome is only those lines or records in the first table that have a record from the second table as a subset regardless of the order.

The challenge is how to join both tables to achieve this result when i run a query


Ade


From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, 30 October 2017, 14:38
Subject: Re: [MS_AccessPros] FIELD JOIN IN QUERY

 
Ade,

It seems your tables aren't normalized correctly. Can you provide more information about what types of values you are storing? I am picturing something like storing EmployeeIDs  (40 different employees) in any of the fields with headings/field names that equate to positions or roles. If this is somewhat like you system, rather than 5 fields, there should be 5 records.

How would you join? Can you provide some sample records and desired output?

Regards,
Duane Hookom


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Adeboyejo Oyenuga aoye_99@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, October 30, 2017 6:02 AM
To: ms_access_professionals@yahoogroups.com
Subject: [MS_AccessPros] FIELD JOIN IN QUERY
 


I am looking for help to create a join between two tables. Fields 1-5 in the first table can take on about 40 different unique values, while Fields 1-3 in the second table can also take on the same number of unique values. Fields 1 to 3 can each equate to any of the values in Fields 1-5 in the 1st table.

Thanks.

Ade

TABLE1
FIELD1 FIELD2 FIELD3 FIELD4 FIELD5
TABLE2
FIELD1 FIELD2 FIELD3






















__._,_.___
Posted by: Adeboyejo Oyenuga <aoye_99@yahoo.co.uk>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (13)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)