Need help calculating queries & reports

[Cam]

Hello,

I have an Access Database 2000 to track performance. Within the db I have a
performance table where user input their ID, date, part#, side, start
sequence &
stop sequence daily (there can be two entries per day). Then on another
reference table with field: part#, side, sequence#, time (min).

What I would like to calculate in the query and report is look at the start
& stop sequence based on the part# & side then calculate how long (time) the
operator spend on the job (part#). Thanks

Example:
Table1
ID date part# side start seq stop seq
001 1/8/08 1360-1 1st 100 500
002 1/10/08 1560-3 2nd 600 1000

Table2
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 15
1360-1 1st 600 60
1560-3 2nd 100 10
...........

Result
query or report output:
ID date part# side start seq stop seq Total time (min)
001 1/8/08 1360-1 1st 100 500 70

Can you give help with query and report design? Thanks

 

[Michel Walsh]

SELECT a.id, a.date, a.partNumber, a.side, a.startSeq, a.stopSeq,
SUM(b.time)

FROM table1 AS a INNER JOIN table2 AS b
ON (a.partNumber=b.partNumber
AND a.side=b.side
AND a.startSeq <= b.startSeq
AND a.stopSeq >= b.stopSeq)

GROUP BY a.id, a.date, a.partNumber, a.side, a.startSeq, a.stopSeq




Hoping it may help,
Vanderghast, Access MVP

 

[Cam]

Michel,

Thanks for the reply.
I am pretty new in INNER JOIN query etc.. where do I put the code you
suggested?
Where in the query or report I put this code or put this on a text field?
Thanks

 

[John W. Vinson]

Where in the query or report I put this code or put this on a text field?

That code IS the query. SQL is the language of queries; the query design grid
is not the query, it's just a tool to build SQL strings.

Create a new Query. Select View... SQL to view the SQL (the real query) and
copy and paste Michel's query. Change the table and fieldnames to your own
table and fieldnames if necessary.

John W. Vinson [MVP]

 

[Cam]

Michel,

What is the a. and b. in your suggested formula? Do I substitue those letter
for my two tables?

 

[Cam]

Michel,

I also ran into a problem with the table naming. I didn't thought about this
initially when I created the database but the table is named tbl_MC-HMC for
table1 and tbl_MC-HMC_Expect for table2. How do I resolve the - character in
the name. It would be very painfull to rename the tables and fix all the
current queries, forms and reports. Is there a way around it.

 

[Cam]

Michel,

Please ignor my previous post as putting a [ ] to the table solve the issue.
But I ran the code and it is calculating incorrectly. The sum of time in
table2 based on the start/ stop sequence is not adding up correctly. Here are
some details of my data that I hope you can sort out for me.

I was wondering whether text field or numeric field from Access have
something to do with why it is not calculating or give incorrect calculation.
Part# and side and start and stop seq from table1 are text field and the
seq# is a text field and time are numeric fields from table2 (ref table).

Also, I forgot to mention that the values in start/ stop seq range from
50 to 9999. The side is either 1st side or 2nd side and the part# has both
letter and number and all part# do not have the same number of characters.

Here is the code I put in. table1 name tbl_MC-HMC, table2 name
tbl_MC-HMC_Expect.

SELECT a.tbl_MCID, a.tbl_Date, a.tbl_Part#, a.tbl_Side, a.tbl_StartSeq,
a.tbl_StopSeq, SUM(b.tbl_Time) AS CalTime
FROM [tbl_MC-HMC] AS a INNER JOIN [tbl_MC-HMC_Expect] AS b ON
(a.tbl_Part#=b.tbl_Part#) AND (a.tbl_Side=b.tbl_Side) AND
(a.tbl_StartSeq<=b.tbl_Sequence) AND (a.tbl_StopSeq>=b.tbl_Sequence)
GROUP BY a.tbl_MCID, a.tbl_Date, a.tbl_Part#, a.tbl_Side, a.tbl_StartSeq,
a.tbl_StopSeq;

 

[Michel Walsh]

You don't substitute these, they are alias, shorter name, less typo, than
using the real table name.


Vanderghast, Access MVP

 

[Michel Walsh]

The fields involved in a not-equal comparison should be a numerical data
type (which may include a date_time field), NOT a string type since string
ordering is rather different than numerical ordering. As example, the
following order is right for strings: "1", "11", "12", "2", "3" while,
for number, it is as expected: 1, 2, 3, 11, 12. So, indeed, >= , > , <
and <= would be wrong if your field is alpha-numerical.

You can try:


WHERE (a.tbl_Part#=b.tbl_Part#) AND (a.tbl_Side=b.tbl_Side) AND
(INT(a.tbl_StartSeq)<=INT(b.tbl_Sequence)) AND
(INT(a.tbl_StopSeq)>=INT(b.tbl_Sequence))





but that will be ***much slower***, at execution time than if the fields
were numerical, in the tables.




Vanderghast, Access MVP

Michel,

Please ignor my previous post as putting a [ ] to the table solve the
issue.
But I ran the code and it is calculating incorrectly. The sum of time in
table2 based on the start/ stop sequence is not adding up correctly. Here
are
some details of my data that I hope you can sort out for me.

I was wondering whether text field or numeric field from Access have
something to do with why it is not calculating or give incorrect
calculation.
Part# and side and start and stop seq from table1 are text field and the
seq# is a text field and time are numeric fields from table2 (ref table).

Also, I forgot to mention that the values in start/ stop seq range from
50 to 9999. The side is either 1st side or 2nd side and the part# has both
letter and number and all part# do not have the same number of characters.

Here is the code I put in. table1 name tbl_MC-HMC, table2 name
tbl_MC-HMC_Expect.

SELECT a.tbl_MCID, a.tbl_Date, a.tbl_Part#, a.tbl_Side, a.tbl_StartSeq,
a.tbl_StopSeq, SUM(b.tbl_Time) AS CalTime
FROM [tbl_MC-HMC] AS a INNER JOIN [tbl_MC-HMC_Expect] AS b ON
(a.tbl_Part#=b.tbl_Part#) AND (a.tbl_Side=b.tbl_Side) AND
(a.tbl_StartSeq<=b.tbl_Sequence) AND (a.tbl_StopSeq>=b.tbl_Sequence)
GROUP BY a.tbl_MCID, a.tbl_Date, a.tbl_Part#, a.tbl_Side, a.tbl_StartSeq,
a.tbl_StopSeq;

SELECT a.id, a.date, a.partNumber, a.side, a.startSeq, a.stopSeq,
SUM(b.time)

FROM table1 AS a INNER JOIN table2 AS b
ON (a.partNumber=b.partNumber
AND a.side=b.side
AND a.startSeq <= b.startSeq
AND a.stopSeq >= b.stopSeq)

GROUP BY a.id, a.date, a.partNumber, a.side, a.startSeq, a.stopSeq




Hoping it may help,
Vanderghast, Access MVP