Re: Need help calculating queries & reports
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Thu, 6 Mar 2008 14:46:26 -0500
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
"Cam" <Cam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2C3D2AE8-1B0F-4975-8551-484CD40DE6EC@xxxxxxxxxxxxxxxx
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" wrote:
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" <Cam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3C5451B8-6CC9-4868-81AD-D0BB287A4BEC@xxxxxxxxxxxxxxxx
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 sequence 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
.
- References:
- Prev by Date: Re: Form Combo Box - Lastname, Firstname, MI
- Next by Date: Re: Using Social Security Numbers, but how do I get rid of duplicates.
- Previous by thread: Re: Need help calculating queries & reports
- Next by thread: Re: Group by truncating field
- Index(es):
Relevant Pages
|
