programming complex query/report

G

Guest

I'm trying to convert some dBaseIV runtime stuff over into Access. I'm not
sure of the right way or easiest way to do this since I'm new to both
programs. The current program takes a couple of tables to use as an index
then searches for those records between two date/time entries. The dates and
times are parameters entered at the beginning of the query. The records
between the date/times entered are then processed to find the total time in
days for each identical ID and the occurences counted. Can you point me in
the right direction and recommend some references to learn from?

A small part of the dBase code I'm converting is below. The whole runtime
programming covers about 1000 pages.


PROCEDURE prtavall
ON KEY LABEL F10 DO Abortproc
mstartime = space(5)
mstoptime = space(5)
mmachid = space(7)
STORE CTOD(" / / ") TO startday
STORE "07:00" TO startime
STORE DATE() TO stopday
STORE "07:00" TO stoptime
USE C:&mdirectory.\&mfilename. ORDER machid IN 1
USE equip ORDER machid IN 2
USE ssa ORDER machtype IN 3
SELECT 1
GO TOP
SELECT 2
GO TOP
SELECT 3
GO TOP
STORE 1 TO run1
DO WHILE run1 = 1
@ 0,01 SAY DBF()
@ 6,00 CLEAR
@ 7,26 SAY "INDICES REPORT INPUT SCREEN"
@ 10,19 SAY "ENTER THE STARTING DATE and TIME:" GET startday PICT
"99/99/9999"
@ 10,65 GET startime PICT "99:99"
@ 12,20 SAY "ENTER THE ENDING DATE and TIME :" GET stopday PICT
"99/99/9999"
@ 12,65 GET stoptime PICT "99:99"
READ
CLEAR GETS
IF startday = CTOD(" / / ")
STORE 0 TO run1
EXIT
ENDIF
@ 6,00 CLEAR
@ 12,32 SAY "PRINTING REPORT"
SET DEVICE TO PRINTER
@ 01,31 SAY "PHOTRONICS - ALLEN"
@ 01,70 SAY DATE()
@ 02,28 SAY "EQUIPMENT INDICES REPORT"
@ 04,16 SAY "PERIOD COVERED:"
@ 04,32 SAY startday
@ 04,43 SAY startime PICT "99:99"
@ 04,49 SAY "TO"
@ 04,52 SAY stopday
@ 04,64 SAY stoptime PICT "99:99"
@ 06,13 SAY "BREAKDOWN RECALL"
@ 07,03 SAY "EQUIP RATE RATE THD OCC. MTBF
MTTR AVAIL"
@ 08,03 SAY REPLICATE("_",76)
STORE 9 TO lineno
SELECT 2
STORE 2 TO run2
DO WHILE run2 = 2
IF EOF()
STORE 0 TO run2
EXIT
ENDIF
IF servflag = "Y"
STORE machid TO mmachid
ELSE
SKIP
LOOP
ENDIF
STORE startday TO mstartday
STORE startime TO mstartime
STORE stopday TO mstopday
STORE stoptime TO mstoptime
mstartime = (VAL(SUBSTR(startime,1,2))+VAL(SUBSTR(startime,4,2))/60)/24
mstoptime = (VAL(SUBSTR(stoptime,1,2))+VAL(SUBSTR(stoptime,4,2))/60)/24
mthd = 0
mperiod = ((mstopday+mstoptime)-(mstartday+mstartime))*24
mpmavail = 0
mcnt = 0
cnt = 0
mtbfcnt = 0
mmssn = 0
ssct = 0
zthd = 0
zmtbfthd = 0
mtbfthd = 0
SELECT 1
GO TOP
SEEK mmachid
SCAN REST FOR (machid = mmachid.AND.daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => startday +
mstartime .AND. ;
daydown + (VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24
<= stopday + mstoptime).OR.(dayup = CTOD(" / / ");
.AND. timedown <> " ").OR.(machid = mmachid .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 =>;
startday + mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24<= stopday +
mstoptime).OR.;
(machid = mmachid.AND.((thd/24) + daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24) => stopday +
mstoptime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= stopday +
mstoptime) WHILE machid = mmachid
IF dayup = CTOD(" / / ").AND.daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= mstartday +
mstartime
STORE mperiod TO zthd
STORE 1 + cnt TO cnt
ELSE
IF (dayup = CTOD(" / / ").AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => mstartday +
mstartime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= mstopday +
mstoptime)
STORE (((mstopday + mstoptime) - (daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24))*24) TO zthd
STORE 1 + cnt TO cnt
ELSE
IF daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= mstartday +
mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 => mstopday +
mstoptime
STORE mperiod TO zthd
STORE 1 + cnt TO cnt
ELSE
IF daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => mstartday +
mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 => mstopday +
mstoptime
STORE (((mstopday + mstoptime) - (daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24))*24) TO zthd
STORE 1 + cnt TO cnt
ELSE
IF dayup + (VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 <=
mstopday + mstoptime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= mstartday +
mstartime
STORE (((dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24) - (mstartday +
mstartime))*24) TO zthd
STORE 1 + cnt TO cnt
ELSE
IF daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => mstartday +
mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 <= mstopday +
mstoptime
STORE thd TO zthd
STORE 1 + cnt TO cnt
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
STORE (mthd + zthd) TO mthd
STORE 0 TO zthd
ENDSCAN
GO TOP
SEEK mmachid
SCAN REST FOR (machid = mmachid.AND.daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => startday +
mstartime .AND. ;
daydown + (VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24
<= stopday + mstoptime .AND. (action = "PM" .OR. action = "SM")) .OR.;
(dayup = CTOD(" / / ") .AND. timedown <> " " .AND. (action =
"PM" .OR. action = "SM")) .OR.( ;
machid = mmachid .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 => startday +
mstartime .AND. ;
dayup + (VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 <=
stopday + mstoptime .AND. (action = "PM" .OR. action = "SM")) .OR.(machid =
mmachid.AND.((thd/24) + daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24) ;
=> stopday + mstoptime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= stopday +
mstoptime .AND. (action = "PM" .OR. action = "SM")) WHILE machid = mmachid
IF dayup = CTOD(" / / ").AND.daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= mstartday +
mstartime
STORE mperiod TO zmtbfthd
STORE 1 + mtbfcnt TO mtbfcnt
ELSE
IF (dayup = CTOD(" / / ").AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => mstartday +
mstartime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= mstopday +
mstoptime)
STORE (((mstopday + mstoptime) - (daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24))*24) TO zmtbfthd
STORE 1 + mtbfcnt TO mtbfcnt
ELSE
IF daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= mstartday +
mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 => mstopday +
mstoptime
STORE mperiod TO zmtbfthd
STORE 1 + mtbfcnt TO mtbfcnt
ELSE
IF daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => mstartday +
mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 => mstopday +
mstoptime
STORE (((mstopday + mstoptime) - (daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24))*24) TO zmtbfthd
STORE 1 + mtbfcnt TO mtbfcnt
ELSE
IF dayup + (VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 <=
mstopday + mstoptime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= mstartday +
mstartime
STORE (((dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24) - (mstartday +
mstartime))*24) TO zmtbfthd
STORE 1 + mtbfcnt TO mtbfcnt
ELSE
IF daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => mstartday +
mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 <= mstopday +
mstoptime
STORE thd TO zmtbfthd
STORE 1 + mtbfcnt TO mtbfcnt
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
STORE (mtbfthd + zmtbfthd) TO mtbfthd
STORE 0 TO zmtbfthd
ENDSCAN
GO TOP
STORE 3 TO run3
SELECT 3
GO TOP
STORE SUBSTR(mmachid,1,3) TO mmachtype
SEEK mmachtype
DO WHILE run3 = 3
IF machtype <> mmachtype .OR. EOF()
STORE 0 TO run3
EXIT
ENDIF
STORE code TO mssc
SELECT 1
GO TOP
SEEK mmachid
SCAN REST FOR
(machid=mmachid.AND.daydown+(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24=>startday+mstartime.AND.daydown+(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24;

<=stopday+mstoptime.AND.subsys=mssc.AND.(action<>"PM".AND.action<>"SM")).OR.(dayup=CTOD("
/ / ").AND.timedown<>"
".AND.subsys=mssc.AND.(action<>"PM".AND.action<>"SM")).OR.;

(machid=mmachid.AND.dayup+(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24=>startday+mstartime.AND.dayup+(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24<=stopday+mstoptime.AND.;

subsys=mssc.AND.(action<>"PM".AND.action<>"SM")).OR.(machid=mmachid.AND.((thd/24)+daydown+(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24)=>stopday+mstoptime.AND.daydown+(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24<=stopday+mstoptime.AND.subsys=mssc.AND.(action<>"PM".AND.action<>"SM")) WHILE machid=mmachid
STORE mmssn + 1 TO mmssn
ENDSCAN
IF mmssn > 0
STORE mmssn -1 TO mmssn
ENDIF
STORE (mmssn + ssct) TO ssct
STORE 0 TO mmssn
SELECT 3
SKIP
ENDDO
mmtbf = ROUND(((mperiod - mthd) + mtbfthd) / (cnt - mtbfcnt),1)
mmmtbf = IIF((cnt = 0).OR.(cnt = mtbfcnt),mperiod,IIF(mmtbf < 0,0,mmtbf))
mmttr = ROUND((mthd-mtbfthd) / (cnt-mtbfcnt),1)
mmmttr = IIF((cnt = 0).OR.(cnt = mtbfcnt),0,IIF(mmttr < 0,0,mmttr))
mavail = ROUND(((mperiod-mthd)/mperiod)*100,1)
mmavail = IIF(mavail < 0,"---",mavail)
mcnt = cnt-mtbfcnt
brate = ROUND((((mthd-mtbfthd)/mthd)*100),1)
mbrate = IIF(mthd = 0,0,brate)
mrr = (ssct/mcnt)*100
mmrr = IIF((ssct = 0).OR.(ssct > mcnt),0,mrr)
@ lineno,03 SAY mmachid PICT "!!!-999"
@ lineno,14 SAY mbrate PICT "999.9%"
@ lineno,23 SAY mmrr PICT "999.9%"
@ lineno,31 SAY mthd PICT "9999.9"
@ lineno,43 SAY mcnt PICT "999"
@ lineno,52 SAY mmmtbf PICT "9999.9"
@ lineno,64 SAY mmmttr PICT "999.9"
@ lineno,72 SAY mmavail PICT "999.9%"
STORE lineno + 1 TO lineno
IF lineno > 54
@ 01,31 SAY "PHOTRONICS - ALLEN"
@ 01,70 SAY DATE()
@ 02,28 SAY "EQUIPMENT INDICES REPORT"
@ 04,16 SAY "PERIOD COVERED:"
@ 04,43 SAY startime PICT "99:99"
@ 04,49 SAY "TO"
@ 04,52 SAY stopday
@ 04,64 SAY stoptime PICT "99:99"
@ 06,13 SAY "BREAKDOWN RECALL"
@ 07,03 SAY "EQUIP RATE RATE THD OCC. MTBF
MTTR AVAIL"
@ 08,03 SAY REPLICATE("_",76)
STORE 9 TO lineno
ENDIF
IF run2 = 2
SELECT 3
GO TOP
SELECT 2
SKIP
LOOP
ENDIF
ENDDO
WAIT
EXIT
ENDDO
CLOSE DATABASES
CLEAR
EJECT
SET DEVICE TO SCREEN
CLEAR
DO Titlebox
@ 0,01 SAY DBF()
@ 5,29 SAY "EQUIPMENT REPORT MENU"
 
O

OfficeDev18 via AccessMonster.com

Hello, Carl,

and welcome to accessmonster.

I have good news and bad news. The good news is that the .DBF files, even
with indeces, are easy to import into Access. The bad news is that the .PRG
and .SCR files cannot be. The .SCR (screen) files will have to be re-designed
manually, using the GUI. The .PRG (program code) files, while they can be
copied and pasted, won't be recognized by any Access import program to be
modified into usable VBA.

To import the .DBF files, open (or create) a database. In the File menu,
click on Get External Data -->Import. On the bottom of the dialog box, change
the 'Files of type' to dBase IV. If the file is indexed, you can choose an
index file (maybe even more than one; I'm not sure). Anyhow, the wizard will
import the file with the index embedded like all Access tables. If you have
trouble importing the index file, lose the index file. It's easier than easy
to create an index in Access.

Hope this helps,

Sam
P.S. I came to Access via dBase and Fox myself, so I know what you're going
through.
 
G

Guest

Hi Sam,
I already have the dbf files imported and have been playing with the
switchboard to get the navigation screens set up. What I don't understand is
how to implement the logic I need, specifically the section below. Once I can
figure that out the rest of the program uses similar subrountines and I
should be able to duplicate it.

SEEK mmachid
SCAN REST FOR (machid = mmachid.AND.daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => startday +
mstartime .AND. ;
daydown + (VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24
<= stopday + mstoptime).OR.(dayup = CTOD(" / / ");
..AND. timedown <> " ").OR.(machid = mmachid .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 =>;
startday + mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24<= stopday +
mstoptime).OR.;
(machid = mmachid.AND.((thd/24) + daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24) => stopday +
mstoptime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= stopday +
mstoptime) WHILE machid = mmachid
 
O

OfficeDev18 via AccessMonster.com

Hi, Carl,

Let's see...

SCAN REST FOR (machid = mmachid.AND.daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 => startday +
mstartime .AND. ;
daydown + (VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24
<= stopday + mstoptime).OR.(dayup = CTOD(" / / ");
..AND. timedown <> " ").OR.(machid = mmachid .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24 =>;
startday + mstartime .AND. dayup +
(VAL(SUBSTR(timeup,1,2))+VAL(SUBSTR(timeup,4,2))/60)/24<= stopday +
mstoptime).OR.;
(machid = mmachid.AND.((thd/24) + daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24) => stopday +
mstoptime .AND. daydown +
(VAL(SUBSTR(timedown,1,2))+VAL(SUBSTR(timedown,4,2))/60)/24 <= stopday +
mstoptime) WHILE machid = mmachid
_______________________________________________________________

SEEK mmachid

You have to open your table as follows for this:

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("YourTableName", dbOpenTable)
rst.Index = "YourIndexName"
With rst
.Seek "=",SomeValue
Do While Not .EOF
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query with date limitations and expressions 1

Top