Re: [belajar-access] Papan informasi digital seperti di Bandara [1 Attachment]

 

[Attachment(s) from hari yanto har_i20002000@yahoo.com [belajar-access] included below]

Untuk membuat papan nama seperti yang terjadi Bandara, setidaknya kita harus memiliki Tabel berupa informasi yang akan disampaikan ke user.

Seperti papan di bandara, kita harus memutuskan:
1. Jumlah record yang akan ditampillkan ke user
2. Jedah waktu up date tampilan

Misal, kita punya tabel bernama t, jumlah record yang akan ditampilkan 4, dan jedah waktu update 1 detik (6000), maka pada form, kita dapat memanfaatkan form timer dan intervaltimer.

Pada saat awal, kita langsung putuskan yang ditampilkan awal adalah yang pertama, selanjutnya ke-2 sampai paling akhir.

itu kerangka berpikirnya. Eksekusi dalam bentuk mdb, bisa dilihat di lampiran.

Semoga bisa membantu dan memberi semangar.

Hariyanto (Surabaya)

--------------------------------------------
On Sat, 30/5/15, Mukti Alie muktifarhan02@yahoo.com [belajar-access] <belajar-access@yahoogroups.com> wrote:

Subject: [belajar-access] Papan informasi digital seperti di Bandara
To: belajar-access@yahoogroups.com
Date: Saturday, 30 May, 2015, 7:48 AM


 









Bapak 2 apakah Ms Access dapat
dibuat Formnya seperti di Papan informasi keberangkatan
pesawat. Jadi datanya dapat berubah2.. Dapat ter update
beberapa detik.. Mohon bantuannya dan contoh
Filenya..









#yiv4235794540 #yiv4235794540 --
#yiv4235794540ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv4235794540 #yiv4235794540ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv4235794540 #yiv4235794540ygrp-mkp #yiv4235794540hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv4235794540 #yiv4235794540ygrp-mkp #yiv4235794540ads {
margin-bottom:10px;}

#yiv4235794540 #yiv4235794540ygrp-mkp .yiv4235794540ad {
padding:0 0;}

#yiv4235794540 #yiv4235794540ygrp-mkp .yiv4235794540ad p {
margin:0;}

#yiv4235794540 #yiv4235794540ygrp-mkp .yiv4235794540ad a {
color:#0000ff;text-decoration:none;}
#yiv4235794540 #yiv4235794540ygrp-sponsor
#yiv4235794540ygrp-lc {
font-family:Arial;}

#yiv4235794540 #yiv4235794540ygrp-sponsor
#yiv4235794540ygrp-lc #yiv4235794540hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv4235794540 #yiv4235794540ygrp-sponsor
#yiv4235794540ygrp-lc .yiv4235794540ad {
margin-bottom:10px;padding:0 0;}

#yiv4235794540 #yiv4235794540actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv4235794540 #yiv4235794540activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv4235794540 #yiv4235794540activity span {
font-weight:700;}

#yiv4235794540 #yiv4235794540activity span:first-child {
text-transform:uppercase;}

#yiv4235794540 #yiv4235794540activity span a {
color:#5085b6;text-decoration:none;}

#yiv4235794540 #yiv4235794540activity span span {
color:#ff7900;}

#yiv4235794540 #yiv4235794540activity span
.yiv4235794540underline {
text-decoration:underline;}

#yiv4235794540 .yiv4235794540attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv4235794540 .yiv4235794540attach div a {
text-decoration:none;}

#yiv4235794540 .yiv4235794540attach img {
border:none;padding-right:5px;}

#yiv4235794540 .yiv4235794540attach label {
display:block;margin-bottom:5px;}

#yiv4235794540 .yiv4235794540attach label a {
text-decoration:none;}

#yiv4235794540 blockquote {
margin:0 0 0 4px;}

#yiv4235794540 .yiv4235794540bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv4235794540 .yiv4235794540bold a {
text-decoration:none;}

#yiv4235794540 dd.yiv4235794540last p a {
font-family:Verdana;font-weight:700;}

#yiv4235794540 dd.yiv4235794540last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv4235794540 dd.yiv4235794540last p
span.yiv4235794540yshortcuts {
margin-right:0;}

#yiv4235794540 div.yiv4235794540attach-table div div a {
text-decoration:none;}

#yiv4235794540 div.yiv4235794540attach-table {
width:400px;}

#yiv4235794540 div.yiv4235794540file-title a, #yiv4235794540
div.yiv4235794540file-title a:active, #yiv4235794540
div.yiv4235794540file-title a:hover, #yiv4235794540
div.yiv4235794540file-title a:visited {
text-decoration:none;}

#yiv4235794540 div.yiv4235794540photo-title a,
#yiv4235794540 div.yiv4235794540photo-title a:active,
#yiv4235794540 div.yiv4235794540photo-title a:hover,
#yiv4235794540 div.yiv4235794540photo-title a:visited {
text-decoration:none;}

#yiv4235794540 div#yiv4235794540ygrp-mlmsg
#yiv4235794540ygrp-msg p a span.yiv4235794540yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv4235794540 .yiv4235794540green {
color:#628c2a;}

#yiv4235794540 .yiv4235794540MsoNormal {
margin:0 0 0 0;}

#yiv4235794540 o {
font-size:0;}

#yiv4235794540 #yiv4235794540photos div {
float:left;width:72px;}

#yiv4235794540 #yiv4235794540photos div div {
border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}

#yiv4235794540 #yiv4235794540photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv4235794540 #yiv4235794540reco-category {
font-size:77%;}

#yiv4235794540 #yiv4235794540reco-desc {
font-size:77%;}

#yiv4235794540 .yiv4235794540replbq {
margin:4px;}

#yiv4235794540 #yiv4235794540ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv4235794540 #yiv4235794540ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv4235794540 #yiv4235794540ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv4235794540 #yiv4235794540ygrp-mlmsg select,
#yiv4235794540 input, #yiv4235794540 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv4235794540 #yiv4235794540ygrp-mlmsg pre, #yiv4235794540
code {
font:115% monospace;}

#yiv4235794540 #yiv4235794540ygrp-mlmsg * {
line-height:1.22em;}

#yiv4235794540 #yiv4235794540ygrp-mlmsg #yiv4235794540logo {
padding-bottom:10px;}

#yiv4235794540 #yiv4235794540ygrp-msg p a {
font-family:Verdana;}

#yiv4235794540 #yiv4235794540ygrp-msg
p#yiv4235794540attach-count span {
color:#1E66AE;font-weight:700;}

#yiv4235794540 #yiv4235794540ygrp-reco
#yiv4235794540reco-head {
color:#ff7900;font-weight:700;}

#yiv4235794540 #yiv4235794540ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv4235794540 #yiv4235794540ygrp-sponsor #yiv4235794540ov
li a {
font-size:130%;text-decoration:none;}

#yiv4235794540 #yiv4235794540ygrp-sponsor #yiv4235794540ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv4235794540 #yiv4235794540ygrp-sponsor #yiv4235794540ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv4235794540 #yiv4235794540ygrp-text {
font-family:Georgia;}

#yiv4235794540 #yiv4235794540ygrp-text p {
margin:0 0 1em 0;}

#yiv4235794540 #yiv4235794540ygrp-text tt {
font-size:120%;}

#yiv4235794540 #yiv4235794540ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv4235794540

__._,_.___

Attachment(s) from hari yanto har_i20002000@yahoo.com [belajar-access] | View attachments on the web

1 of 1 File(s)

papanBandara.zip

---

Posted by: hari yanto <har_i20002000@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

SPAM IS PROHIBITED

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

Re: [belajar-access] record number dalam access [1 Attachment]

 

[Attachment(s) from Yokanan Wahyono wahyono17@gmail.com [belajar-access] included below]

terima kasih
sudah ketemu mengutip dari yg pernah di bahas di milis ini
berikut saya lampirkan programnya

On 29/05/2015 21:51, hari yanto har_i20002000@yahoo.com [belajar-access] wrote:

 

Yokanan Wahyono ....,

Ada yang terewat dari data Sampeyan. Tampilan di Excel tersebut sudah di urutkan terlebih dahulu. Artinya, pada masing-masing nilai ada id uniknya.

Kalau sudah ada id uniknya, kita tinggal membuat query:
"select id from namaTabel where kode=" & ko & " order by id asc"
Disini kita bisa mendenisikan nilai urut dengan cara do while. Apabila id-nya sama dengan yang ditunjuk, kita hentikan dengan cara exit do.

Terlampir adalah contoh kongkretnya.

Semoga bisa membantu dan memberi semangat.

Hariyanto (Surabaya)

--------------------------------------------
On Fri, 29/5/15, Yokanan Wahyono wahyono17@gmail.com [belajar-access] <belajar-access@yahoogroups.com> wrote:

Subject: [belajar-access] record number dalam access [1 Attachment]
To: belajar-access@yahoogroups.com
Date: Friday, 29 May, 2015, 9:32 AM


 









[Attachment(s) from Yokanan Wahyono
wahyono17@gmail.com [belajar-access] included below]





Dear semua



bagaimana cara menuangkan rumus excel di bawah ini ke
dalam access

kodenamarecordnumber541meja1541meja2541meja3231kursi1231kursi2401lemari1401lemari2401lemari3401lemari4


record number akan bertambah jika kodenya sama

jika kode di bawahnya beda akan reset ke 1

saya coba pakai quey tapi tidak bisa reset ke 1 jika
kode beda



salam

yokanan















#yiv6946690494 #yiv6946690494 --
#yiv6946690494ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv6946690494 #yiv6946690494ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv6946690494 #yiv6946690494ygrp-mkp #yiv6946690494hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv6946690494 #yiv6946690494ygrp-mkp #yiv6946690494ads {
margin-bottom:10px;}

#yiv6946690494 #yiv6946690494ygrp-mkp .yiv6946690494ad {
padding:0 0;}

#yiv6946690494 #yiv6946690494ygrp-mkp .yiv6946690494ad p {
margin:0;}

#yiv6946690494 #yiv6946690494ygrp-mkp .yiv6946690494ad a {
color:#0000ff;text-decoration:none;}
#yiv6946690494 #yiv6946690494ygrp-sponsor
#yiv6946690494ygrp-lc {
font-family:Arial;}

#yiv6946690494 #yiv6946690494ygrp-sponsor
#yiv6946690494ygrp-lc #yiv6946690494hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv6946690494 #yiv6946690494ygrp-sponsor
#yiv6946690494ygrp-lc .yiv6946690494ad {
margin-bottom:10px;padding:0 0;}

#yiv6946690494 #yiv6946690494actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv6946690494 #yiv6946690494activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv6946690494 #yiv6946690494activity span {
font-weight:700;}

#yiv6946690494 #yiv6946690494activity span:first-child {
text-transform:uppercase;}

#yiv6946690494 #yiv6946690494activity span a {
color:#5085b6;text-decoration:none;}

#yiv6946690494 #yiv6946690494activity span span {
color:#ff7900;}

#yiv6946690494 #yiv6946690494activity span
.yiv6946690494underline {
text-decoration:underline;}

#yiv6946690494 .yiv6946690494attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv6946690494 .yiv6946690494attach div a {
text-decoration:none;}

#yiv6946690494 .yiv6946690494attach img {
border:none;padding-right:5px;}

#yiv6946690494 .yiv6946690494attach label {
display:block;margin-bottom:5px;}

#yiv6946690494 .yiv6946690494attach label a {
text-decoration:none;}

#yiv6946690494 blockquote {
margin:0 0 0 4px;}

#yiv6946690494 .yiv6946690494bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv6946690494 .yiv6946690494bold a {
text-decoration:none;}

#yiv6946690494 dd.yiv6946690494last p a {
font-family:Verdana;font-weight:700;}

#yiv6946690494 dd.yiv6946690494last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv6946690494 dd.yiv6946690494last p
span.yiv6946690494yshortcuts {
margin-right:0;}

#yiv6946690494 div.yiv6946690494attach-table div div a {
text-decoration:none;}

#yiv6946690494 div.yiv6946690494attach-table {
width:400px;}

#yiv6946690494 div.yiv6946690494file-title a, #yiv6946690494
div.yiv6946690494file-title a:active, #yiv6946690494
div.yiv6946690494file-title a:hover, #yiv6946690494
div.yiv6946690494file-title a:visited {
text-decoration:none;}


#yiv6946690494 div.yiv6946690494photo-title a,
#yiv6946690494 div.yiv6946690494photo-title a:active,
#yiv6946690494 div.yiv6946690494photo-title a:hover,
#yiv6946690494 div.yiv6946690494photo-title a:visited {
text-decoration:none;}

#yiv6946690494 div#yiv6946690494ygrp-mlmsg
#yiv6946690494ygrp-msg p a span.yiv6946690494yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv6946690494 .yiv6946690494green {
color:#628c2a;}

#yiv6946690494 .yiv6946690494MsoNormal {
margin:0 0 0 0;}

#yiv6946690494 o {
font-size:0;}

#yiv6946690494 #yiv6946690494photos div {
float:left;width:72px;}

#yiv6946690494 #yiv6946690494photos div div {

border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}

#yiv6946690494 #yiv6946690494photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv6946690494 #yiv6946690494reco-category {
font-size:77%;}

#yiv6946690494 #yiv6946690494reco-desc {
font-size:77%;}

#yiv6946690494 .yiv6946690494replbq {
margin:4px;}

#yiv6946690494 #yiv6946690494ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv6946690494 #yiv6946690494ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv6946690494 #yiv6946690494ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv6946690494 #yiv6946690494ygrp-mlmsg select,
#yiv6946690494 input, #yiv6946690494 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv6946690494 #yiv6946690494ygrp-mlmsg pre, #yiv6946690494
code {
font:115% monospace;}

#yiv6946690494 #yiv6946690494ygrp-mlmsg * {
line-height:1.22em;}

#yiv6946690494 #yiv6946690494ygrp-mlmsg #yiv6946690494logo {
padding-bottom:10px;}


#yiv6946690494 #yiv6946690494ygrp-msg p a {
font-family:Verdana;}

#yiv6946690494 #yiv6946690494ygrp-msg
p#yiv6946690494attach-count span {
color:#1E66AE;font-weight:700;}

#yiv6946690494 #yiv6946690494ygrp-reco
#yiv6946690494reco-head {
color:#ff7900;font-weight:700;}

#yiv6946690494 #yiv6946690494ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv6946690494 #yiv6946690494ygrp-sponsor #yiv6946690494ov
li a {
font-size:130%;text-decoration:none;}

#yiv6946690494 #yiv6946690494ygrp-sponsor #yiv6946690494ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv6946690494 #yiv6946690494ygrp-sponsor #yiv6946690494ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv6946690494 #yiv6946690494ygrp-text {
font-family:Georgia;}

#yiv6946690494 #yiv6946690494ygrp-text p {
margin:0 0 1em 0;}

#yiv6946690494 #yiv6946690494ygrp-text tt {
font-size:120%;}

#yiv6946690494 #yiv6946690494ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv6946690494

__._,_.___

Attachment(s) from Yokanan Wahyono wahyono17@gmail.com [belajar-access] | View attachments on the web

1 of 1 File(s)

looping.mdb

---

Posted by: Yokanan Wahyono <wahyono17@gmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

SPAM IS PROHIBITED

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

[belajar-access] Papan informasi digital seperti di Bandara

 

Bapak 2 apakah Ms Access dapat dibuat Formnya seperti di Papan informasi keberangkatan pesawat. Jadi datanya dapat berubah2.. Dapat ter update beberapa detik.. Mohon bantuannya dan contoh Filenya..

__._,_.___

---

Posted by: Mukti Alie <muktifarhan02@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

SPAM IS PROHIBITED

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

RE: [MS_AccessPros] Re: #Name? on report

 

Duane,

I thought that was implied when you mentioned a module. I couldn't think of another reason. Yes it does compile with no errors.

I include the info I think is relevant. If I had known there was a significant difference, I would've specified. FWIW, CompDate was in the page header and was referred to in both the Page and Report Header. I've moved it to the Report Header and refer to it in the Page Header, and it works.

This doesn't explain the issue with Product. I think John's explanation does.

Adam

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Adam,
 
I don't believe I suggested "create a report in a module". I only wanted you to make sure any code in your application compiles with no errors.
 
When referencing report header and footer sections there is a significant difference between Page and Report/Group sections. Please be specific when you post question relating to these.
 
Duane
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 28 May 2015 09:25:59 -0700
Subject: [MS_AccessPros] Re: #Name? on report



John,


I think that was the problem. Before I read it, I got it to work by putting [CompDate] in the header. That seemed to work as long as it was in the header or detail, but not the footer. "Totals for " & [Product] started working again on its own. I'll try your fix next time.


Duane,


It also happened with [Product] which was just part of a concatenation, not a function. I don't know how to create a report in a module. Is that possible? More efficient?


Thanks,


Adam

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Adam,
 
I would open a module and try to compile your code. If the issue is always with DateSerial(), I would search your code for another function with the same name.
 
Duane Hookom, MVP
MS Access
 

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 28 May 2015 06:52:20 -0700
Subject: [MS_AccessPros] #Name? on report



I'm getting this on calculated fields in headers and footers, but not all calculated fields.


Header: I have a series of 12 monthly columns. The source for the first column is CompDate, a field on the report's source query. That field shows up correctly. The second column's source is =DateSerial(Year([CompDate]),Month([CompDate])-1,1); the third is =DateSerial(Year([CompDate]),Month([CompDate])-2,1); etc. #Name? shows up on the other 11 columns.


Footer: #Name? shows up in a field with the source ="Totals for " & [Product] where [Product] is a field in the report's source query, and in the detail (grouped by Product. Other Sum and Count calculated fields in the footer are fine.


This is happening on other reports with similar fields. These reports worked yesterday. Forms with the same sources and fields are working. I'm baffled. Any ideas?


Adam

__._,_.___

---

Posted by: runuphillracing@yahoo.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (6)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___

Re: [MS_AccessPros] Weird problem with Query- different results for same field

 

Glad to hear we got it to work!

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On May 29, 2015, at 5:14 PM, pattykf@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

WOW that was great, returned in seconds

I like the formatting of the first query, I can use that with other queries I need to develop

thank you so much, live savor as usual!!! Have a wonderful weekend

really appreciate you help, you are so gracious with your time and expertise.  I just need to get more done in your book to expand my knowledge, there just are not enough hours in the day.

THANK YOU

Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

I was afraid of that.  The problem is the query engine has to run the subquery (the C2 stuff) once for each row returned by the main query.  If you have a lot of rows, and especially if there's no index on modify_timestamp or person_id, it can take a long time.

Let's try a different method:

First, build a query like this:

qryMaxChart

SELECT dbo_wo_chart1_.*

FROM dbo_wo_chart1_ INNER JOIN 

(SELECT person_id, MAX(modify_timestamp) As TimeMatch

FROM dbo_wo_chart1_ As C2

WHERE C2.PCP_DBP Is Not Null 

AND C2.exclude_from_reporting < 1

GROUP BY C2.person_id) As MaxRecord

ON dbo_wo_chart1_.person_id = MaxRecord.person_id

AND dbo_wo_chart1_.modify_timestamp = MaxRecord.TimeMatch;

Now try this modified final result query:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, qryMaxChart.insurance1, qryMaxChart.insurance2, qryMaxChart.PCP_DBP, qryMaxChart.modify_timestamp, qryMaxChart.asthma_persist_ind, qryMaxChart.enc_id
FROM (dbo_person RIGHT JOIN qryMaxChart ON dbo_person.person_id = qryMaxChart.person_id) LEFT JOIN Pt_Status ON qryMaxChart.person_id = Pt_Status.person_id
WHERE (((qryMaxChart.PCP_DBP) Is Not Null) AND ((qryMaxChart.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null)) AND ((qryMaxChart.modify_timestamp)>#4/1/2013 0:01:01#)

What I have done is use a more efficient INNER JOIN to extract the chart records with the latest time stamp for each person, THEN put that result in your original query.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On May 28, 2015, at 11:20 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John

I tried to run it and it just stalled like it was running but never returned anything,

I pasted it here for you.

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null)) AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:01:01#) AND dbo_wo_chart1_.modify_timestamp = (SELECT MAX(modify_timestamp) FROM dbo_wo_chart1_ As C2 WHERE C2.person_id = dbo_wo_chart1_.person_id AND C2.PCP_DBP Is Not Null AND C2.exclude_from_reporting < 1)
ORDER BY dbo_wo_chart1_.PCP_DBP;

I have tried to see if I can find something that might have a spelling issue, or space or ) but nothing I seem to try makes it finish the query run

sorry, thought we had this one

I just am not sure what else to try to change, I am getting a better grasp on the C2 feature so that is a plus on this one :)

Patty
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

There's an error in my SQL.  Try this:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))

AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:1:1#))

AND dbo_wo_chart1_.modify_timestamp = (SELECT MAX(modify_timestamp) FROM dbo_wo_chart1_ As C2 WHERE C2.person_id = dbo_wo_chart1_.person_id AND C2.PCB_DBP Is Not Null AND C2.exclude_from_reporting < 1)
ORDER BY dbo_wo_chart1_.PCP_DBP;

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On May 28, 2015, at 8:54 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John

I have a problem it is asking for parameter information for: Modify timestamp , at first I thought maybe the C2.person_id=dbo_wo_chart1_modify_timestamp was the issue but changing that to person_id (although I think that needs to change unless I do not understand the C2 still) did not stop the parameter popup

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:1:1# And (dbo_wo_chart1_.modify_timestamp)=(SELECT MAX(modify_timestamp) FROM dbo_wo_chart1_ As C2 WHERE C2.person_id = dbo_wo_chart1_modify_timestamp AND C2.PCP_DBP Is Not Null AND C2.exclude_from_reporting < 1)) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))
ORDER BY dbo_wo_chart1_.PCP_DBP;

I have tried to see what I could fix but it is beyond my skills,

thank you so much

Patty

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

thank you so much John,
I didn't make the table but I am sure the person who did was thinking it would have something after the _, It is actually the data from a table in an electronic health record that shows the clinical staff the patient info, insurance and primary care provider so that the information could be verified before moving on in the record. that way they are entering on the correct patient (electronic health records are not well designed to say the least).
I will run the queries in a bit, multitasking right now, very much appreciate your help
Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

OK, let's start by adding the date filter to your original query:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))

AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:1:1#))
ORDER BY dbo_wo_chart1_.PCP_DBP;

Now, let's find the last one:

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))

AND ((dbo_wo_chart1_.modify_timestamp)>#4/1/2013 0:1:1#))

AND dbo_wo_chart1_.modify_timestamp = (SELECT MAX(modify_timestamp) FROM dbo_wo_chart1_ As C2 WHERE C2.person_id = dbo_wo_chart1_modify_timestamp AND C2.PCB_DBP Is Not Null AND C2.exclude_from_reporting < 1)
ORDER BY dbo_wo_chart1_.PCP_DBP;

By the way, who invented the funky table names?  A table name ending in an underscore??  (dbo_wo_chart1_)  Gimme a break!

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On May 26, 2015, at 7:15 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Ok to the basics,
I have 3 tables, the chart 1, person and status (status will identify those that are not active patients)
so pulled in the patients with data on chart 1 and linked it up to find out who they were (person) and that they were active pts

so ultimately I want the patients on chart1, that are active and what the last information for insurance 1 and 2 and PCP_DBP (excluding those pts without information in PCP_DBP)

SELECT dbo_person.person_id, dbo_person.person_nbr, dbo_person.last_name, dbo_person.first_name, dbo_person.date_of_birth, dbo_person.sex, dbo_wo_chart1_.insurance1, dbo_wo_chart1_.insurance2, dbo_wo_chart1_.PCP_DBP, dbo_wo_chart1_.modify_timestamp, dbo_wo_chart1_.asthma_persist_ind, dbo_wo_chart1_.enc_id
FROM (dbo_person RIGHT JOIN dbo_wo_chart1_ ON dbo_person.person_id = dbo_wo_chart1_.person_id) LEFT JOIN Pt_Status ON dbo_wo_chart1_.person_id = Pt_Status.person_id
WHERE (((dbo_wo_chart1_.PCP_DBP) Is Not Null) AND ((dbo_wo_chart1_.exclude_from_reporting)<1) AND ((Pt_Status.description) Is Null))

ORDER BY dbo_wo_chart1_.PCP_DBP;


I have been trying to do it one step at a time since I am not confident in my query skills. I thought if I did it one step at a time it was less likely that I pulled in the wrong data.

Patty

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

First, LAST won't work - it returns the last physically stored record, which in many cases will not be the record with the highest value, particularly if there have ever been any record deletions.  It's not reliable.

Is Chart_1_filter also a query?  If so, I need to see the SQL for that.  Let's get down to brass tacks.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On May 26, 2015, at 6:35 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I was wondering if this would also give me the last time the information was entered on a person?

SELECT Chart1_Filter_date.person_id, Last(Chart1_Filter_date.modify_timestamp) AS LastOfmodify_timestamp, Last(Chart1_Filter_date.person_nbr) AS LastOfperson_nbr, Last(Chart1_Filter_date.insurance1) AS LastOfinsurance1, Last(Chart1_Filter_date.insurance2) AS LastOfinsurance2, Last(Chart1_Filter_date.PCP_DBP) AS LastOfPCP_DBP
FROM Chart1_Filter_date
GROUP BY Chart1_Filter_date.person_id
ORDER BY Last(Chart1_Filter_date.modify_timestamp) DESC;

or would this be flawed in design?
Patty

---In MS_Access_Professionals@yahoogroups.com, <pattykf@...> wrote :

Ok that makes more since, this is the query that the max one is pulling from

Chart1_Filter_date is below:

SELECT Chart_1_filter.person_id, Chart_1_filter.person_nbr, Chart_1_filter.last_name, Chart_1_filter.first_name, Chart_1_filter.date_of_birth, Chart_1_filter.sex, Chart_1_filter.insurance1, Chart_1_filter.insurance2, Chart_1_filter.PCP_DBP, Chart_1_filter.modify_timestamp, Chart_1_filter.asthma_persist_ind, Chart_1_filter.enc_id
FROM Chart_1_filter
WHERE (((Chart_1_filter.PCP_DBP) Is Not Null) AND ((Chart_1_filter.modify_timestamp)>#4/1/2013 0:1:1#));

thankx so much,

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Patty-

The (SELECT …. ) stuff in the WHERE clause is a subquery.  That subquery is looking in the same "table" (query in this case) as you're using in the main FROM clause, and we're looking for the highest value from the person_id found in the outer query current row.  The AS C2 assigns an alias name to the subquery table so that we can clearly identify that we want the person_id from the table in the subquery (C2.person_id) matches the current person_id in the main query recordset (Chart1_Filter_date.person_id).

What is the SQL of Chart1_Filter_date??

There's something in that query that is causing the problem.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On May 26, 2015, at 5:46 PM, pattykf@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (24)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___