473,372 Members | 1,103 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Opposite of Union Query

I saw a post about a year ago, that pondered if an opposite to a union query was possible, i.e. an intersect query. however, the OP was concerned about varous fields intersectiong with one another.

i have a different approach.

i have a table of records. there is a date field a start time field and an end time field. (not together, due to outside programming compatability issues) i want to be able to sort through the records through a block of time. i.e. all records that started on day 1 after 3 am, but finished on day 2 before 6 pm.

i tired the having clause, but i don't have an aggregarate function. i tried a where date>start tate and start time >= start time and date <= end date and endtime <= end time.

hwoever, if if something happened after the end time after the start date, but was before the end date, it won't show up,

say i have these events

Date Start Time end time
1/1/2000 1:00 PM 1:53 PM
1/1/2000 6:53 PM 7:35 PM
1/2/200 4:45 PM 5:30 PM

and i wanted events between noon on 1/1/2000 and 6:00 PM on 1/2/200, i would miss 1/1/200 6:53 because the end time was after end time for the econd date, but it was still in my range of accecpted values.
Nov 28 '06 #1
17 6172
nico5038
3,080 Expert 2GB
First I would like to advise you to change the fieldname Date into another name, e.g. RecDate.
Next you need to make sure to use a WHERE clause and that the comparison is performed on a datefield with a time, else Access will use 00:00:00 for the time...

When still uncertain, post your query with a description of the erroneous results here so we can have a look.

Nic;o)
Nov 28 '06 #2
NeoPa
32,556 Expert Mod 16PB
As we don't have the comparison dates and times you want to select from I'll use a couple of prompt fields :
[Enter Start Date/Time] and [Enter End Date/Time].
I'm also assuming that the [Date] field you mention is in Date/time format but with no time part and that [Start Time] and [End Time] are strings.
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Table
  3. WHERE (([Enter End Date/Time]>=[Date] + CDate([Start Time]))
  4.   AND ([Enter Start Date/Time]<=[Date] + CDate([End Time])))
I've used your field name ([Date]) here but support Nico's suggestion that it's a name to be avoided as you'd want to avoid the problems it will bring you.
Nov 28 '06 #3
i know i shouldn't use date as a field name, however, that's the way the database needed to be set up. easier for me to wrie a few lines of VBA to deal with the date as a field name, than to change a lot of c code to fix the same problem. (not my doing, i just do what i'm told)

next, here's my VBA code to produce the string.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT tblCoils.Time, tblCoils.Date, tblCoils.CoilID, tblCoils.CoilDataTable, tblCoils.Grade, tblCoils.Furnace"
  2. strSQL = strSQL + ", tblCoils.TapA, tblCoils.TapB, tblCoils.TapC, tblCoils.TapD, tblCoils.Program, tblCoils.VaLimit"
  3. strSQL = strSQL + ", tblCoils.IaLimit, tblCoils.KwLimit, tblCoils.ShimsFld, tblCoils.BowFld, tblCoils.HeatingDelay"
  4. strSQL = strSQL + ", tblCoils.OperHeatDelay, tblCoils.VMMString, tblCoils.TargetDoTemp, tblCoils.ActualDoTemp, tblCoils.PeakFront"
  5. strSQL = strSQL + ", tblCoils.PeakBack, tblCoils.[4thPassTemp], tblCoils.HeatStartTime, tblCoils.FinishTime, tblCoils.TotalHoldTime"
  6. strSQL = strSQL + ", tblCoils.LastFinishTime, tblCoils.StandardHeatTime, tblCoils.Thickness, tblCoils.Width, tblCoils.Weight"
  7. strSQL = strSQL + ", tblCoils.MinFront, tblCoils.MinBack, tblCoils.ChargeTemp, tblCoils.Report FROM tblCoils "
  8. strSQL = strSQL + "WHERE (((tblCoils.Date)>=#" & [StartDate] & "# And (tblCoils.Date)<=#" & [EndDate] & "#) AND ((tblCoils.HeatStartTime)>=#" & [StartTime] & "#) AND ((tblCoils.FinishTime)<=#" & [EndTime] & "#));"
the form has 3 data entr boxes, StartDate, EndDate, StartTime, EndTime.
This is what i'm using for my data entry
SD = 12/15/03
ST= 2AM
ED=11/20/06
ET=11:53PM
(note, these are correctlly entyered in the form. the dates and times are Medium formats)

my records have the follwoing Fields (Date - ST - ET)
1)12/15/2003 ----- 11:18:43 P ---- 1:48:AM (next day)
2)6/30/90 ----------- 2:48:43 P ----- 5:18:43 P
3)1/5/93 ------------- 3:48:43 P ----- 7:18:43 P
4)2/18/95 ----------- 4:48:43 P ----- 5:14:43 P
5)11/20/2006 ------ 5:48:443 P --- 7:18:43 P
6)11/28/2004 ------ 1:30:00 A ---- 11:55:00 PM
7)9/14/2006 -------- 6:48:43 P ---- 11:18:43 P

i should get records 1, 5, 6, 7 however i don't get record 6 back.

is that clear?
Nov 29 '06 #4
nico5038
3,080 Expert 2GB
Hmm, lets try for the WHERE clause:

strSQL = strSQL + "WHERE (tblCoils.Date + tblCoils.HeatStartTime between #" & [StartDate] & " " & [StartTime]"# and #" & [EndDate] & " " & [EndTime] & "#) And tblCoils.Date + tblCoils.FinishTime between #" & [StartDate] & " " & [StartTime]"# and #" & [EndDate] & " " & [EndTime] & "#));"

This constructs with the + a datetime start and end field.
The startdate is tested to be between the start and enddate and so is the the enddate. Thus only periods within the start/end datetime will be selected.

Nic;o)
Nov 29 '06 #5
ok, so i did some reasearch, and found thought of a round about way of getting what i want. i add a query and a table to my DB.

(i guess i don't need the query because of the basic statements i use, let me know though)

i add a table called 'tblShift' and a query called 'qryShiftTable'

the query will be both a delte, then add, query. i'll delete all old contents, then add the tblCoils (in its entirety) back to it. then i'll cycle through the records to pick and chose what i want. (it should be obvious from my code)

then my orginial query, qrySFRForm, just contains all records in the tblShift table.

so here,s my code for the matter (and it does work)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.  
  3. Dim strSQL As String
  4. Dim dbsC1894 As Database
  5. Dim rstShift As Recordset
  6.  
  7. Set dbsC1894 = CurrentDb
  8.  
  9. strSQL = "DELETE FROM tblShift;"
  10.  
  11. dbsC1894.Execute (strSQL)
  12.  
  13. strSQL = "INSERT INTO tblShift ( [Time], [Date], CoilID, CoilDataTable, Grade, Furnace, TapA, TapB, TapC, TapD, Program, VaLimit, IaLimit"
  14. strSQL = strSQL + ", KwLimit, ShimsFld, BowFld, HeatingDelay, OperHeatDelay, VMMString, TargetDoTemp, ActualDoTemp, PeakFront, PeakBack"
  15. strSQL = strSQL + ", 4thPassTemp, HeatStartTime, FinishTime, TotalHoldTime, LastFinishTime, StandardHeatTime, Thickness, Width, Weight"
  16. strSQL = strSQL + ", MinFront, MinBack, ChargeTemp, Report ) "
  17. strSQL = strSQL + "SELECT tblCoils.Time, tblCoils.Date, tblCoils.CoilID, tblCoils.CoilDataTable, tblCoils.Grade, tblCoils.Furnace"
  18. strSQL = strSQL + ", tblCoils.TapA, tblCoils.TapB, tblCoils.TapC, tblCoils.TapD, tblCoils.Program, tblCoils.VaLimit, tblCoils.IaLimit"
  19. strSQL = strSQL + ", tblCoils.KwLimit, tblCoils.ShimsFld, tblCoils.BowFld, tblCoils.HeatingDelay, tblCoils.OperHeatDelay, tblCoils.VMMString"
  20. strSQL = strSQL + ", tblCoils.TargetDoTemp, tblCoils.ActualDoTemp, tblCoils.PeakFront, tblCoils.PeakBack, tblCoils.[4thPassTemp]"
  21. strSQL = strSQL + ", tblCoils.HeatStartTime, tblCoils.FinishTime, tblCoils.TotalHoldTime, tblCoils.LastFinishTime, tblCoils.StandardHeatTime"
  22. strSQL = strSQL + ", tblCoils.Thickness, tblCoils.Width, tblCoils.Weight, tblCoils.MinFront, tblCoils.MinBack, tblCoils.ChargeTemp"
  23. strSQL = strSQL + ", tblCoils.Report FROM tblCoils;"
  24.  
  25. dbsC1894.Execute (strSQL)
  26.  
  27. Set rstShift = dbsC1894.OpenRecordset("tblShift")
  28.  
  29. rstShift.MoveFirst
  30.  
  31. Do Until rstShift.EOF
  32.  
  33. If rstShift!Date < [StartDate] Then
  34.     rstShift.Delete
  35.  
  36. Else
  37.     If rstShift!Date > [EndDate] Then
  38.         rstShft.Delete
  39.     End If
  40. End If
  41.  
  42. rstShift.MoveNext
  43. Loop
  44.  
  45. rstShift.Close
  46.  
  47. Set rstShift = dbsC1894.OpenRecordset("tblShift")
  48.  
  49. rstShift.MoveFirst
  50.  
  51. Do Until rstShift.EOF
  52.  
  53. If rstShift!Date = [StartDate] Then
  54.     If rstShift!HeatStartTime < [StartTime] Then
  55.         rstShift.Delete
  56.     End If
  57. End If
  58.  
  59. rstShift.MoveNext
  60. Loop
  61. rstShift.Close
  62.  
  63.  
  64. Set rstShift = dbsC1894.OpenRecordset("tblShift")
  65.  
  66. rstShift.MoveFirst
  67.  
  68. Do Until rstShift.EOF
  69.  
  70. If rstShift!Date = [EndDate] Then
  71.     If rstShift!FinishTime > [EndTime] Then
  72.         rstShift.Delete
  73.     End If
  74. End If
  75.  
  76. rstShift.MoveNext
  77. Loop
  78. rstShift.Close
  79.  
  80. End Sub
so this worked oout, a bit more than i wanted to do, but the queries and whatnot are not time criticle, and there would never be more than say 2000 records (and that's really pushing it, it would probably be uner 1k due to production times and maitenance)
Nov 29 '06 #6
nico5038
3,080 Expert 2GB
Much work and using a recordset processing loop is a lot slower than a query.

Did you try the other where clause ?

Nic;o)
Nov 29 '06 #7
yeah, i tried the whereclause, but there were parenthese issues. i'm going through now, trying to determine where they are needed, and such.

Much work and using a recordset processing loop is a lot slower than a query.

Did you try the other where clause ?

Nic;o)
Nov 29 '06 #8
i edited the whereclause to be strSQL = strSQL + "WHERE ((tblCoils.Date + tblCoils.HeatStartTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#) And (tblCoils.Date + tblCoils.FinishTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#));"

that queried, but i didn't get the 2003 now.


yeah, i tried the whereclause, but there were parenthese issues. i'm going through now, trying to determine where they are needed, and such.
Nov 29 '06 #9
i edited the whereclause to be strSQL = strSQL + "WHERE ((tblCoils.Date + tblCoils.HeatStartTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#) And (tblCoils.Date + tblCoils.FinishTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#));"

that queried, but i didn't get the 2003 now. i think it's because the FinishTime is less than the start time (due to it carring over to the next day). i.e. when Date+finishtime must be between SD.ST and ED/ET, it sees that date+finish when it is, but the finish time is actually the next day, but the code views as the project starting at 11 PM on that day, but Ending at 1:40 AM of the same day, i.e. before it started.

do you see what i'm getting at?

sorry, threads won't let me edit after 5 minutes
Nov 29 '06 #10
nico5038
3,080 Expert 2GB
OK, you're paying the price for not using two date/time fields :-)

Try:
strSQL = strSQL + "WHERE ((tblCoils.Date + tblCoils.HeatStartTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#) And
(tblCoils.Date + tblCoils.FinishTime) + IIF((tblCoils.Date + tblCoils.FinishTime)<(tblCoils.Date + tblCoils.HeatStartTime),1,0) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#));"

The added " + IIF(end < start,1,0) " part will add an additional day when the enddate is less as the startdate.

Nic;o)
Nov 29 '06 #11
NeoPa
32,556 Expert Mod 16PB
Just curious, why did you not just use the WHERE clause I laid out earlier?
Did you find it not to work in any way?
Nov 30 '06 #12
well, because i didn't understand your vauge explination. next, i know how the d/t works, and yes, i know it's stupid to not have the time and date in the same field (as i said abefore, it's easier for me to deal with it, than to rewrite an entire control program)

i'm gonna try nicos suggestion. i think that may do the trick.

Just curious, why did you not just use the WHERE clause I laid out earlier?
Did you find it not to work in any way?
Nov 30 '06 #13
NeoPa
32,556 Expert Mod 16PB
well, because i didn't understand your vauge explination.
I'm sorry my explanation doesn't match up to your specification.
You should really demand your money back.

I hope it's clear that I find your attitude offensive and reflecting an extraordinary lack of good manners.

It's funny though, I can piece together your response even notwithstanding the apparently random spelling.
Nov 30 '06 #14
MMcCarthy
14,534 Expert Mod 8TB
well, because i didn't understand your vauge explination. next, i know how the d/t works, and yes, i know it's stupid to not have the time and date in the same field (as i said abefore, it's easier for me to deal with it, than to rewrite an entire control program)

i'm gonna try nicos suggestion. i think that may do the trick.
shades234,

Our experts spend some time working out answers to your questions. As this is their own time, which they volunteer, I think it would be more appropriate for you to say ...

"Thank you but I feel the other code would better suit me"

or something along those lines.

It is not good to criticise someone who is trying to help you.
Nov 30 '06 #15
well, nico's code worked great. Thank you.

as for everyone else, good job trying. better luck next time.

i never ment to "belittle" or "insult" or whatever anyone's post. in my haste of writing a response, it may appear more hurtful than it really is. all i was attempting to convey was i was unsure of NeoPa's answer. since i had actual lines of code from another poster, i was focusing more on that, only because i can see the flow of the code, rather than the flow of "suggestion." what would you rather follow someone who gave you a good set of specifications and a base blueprint, or someone who said, use a door, some 2x4s, windows, plywood, and shingles. I, for one, would take the blueprint. maybe that's just me and how i work.

I'm sorry if this bothers anyone. it's just how i am.

once again, thanks
Dec 1 '06 #16
NeoPa
32,556 Expert Mod 16PB
I appreciate your point, but when requesting help and someone's time, the least you should do is test and respond to all attempts.
In fact it would have saved everyone some time, but at the end of the day - I'll leave you to worry about your manners.
Dec 1 '06 #17
nico5038
3,080 Expert 2GB
Glad I could help.

I see from the other comments that we proved once again that solving problems just using text is hard. A good advice is to keep asking when a comment isn't clear. Assumptions are in this business "killing" and easily lead to unnecessary flames that only produce losers.

Success with your application !

Nic;o)
Dec 1 '06 #18

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Paradigm | last post by:
I am using Access 2K as a front end to a MYSQL database. I am trying to run a Union query on the MYSQL database. The query is (much simplified) SELECT as ID from faxdata UNION SELECT as ID ...
2
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code ...
6
by: bradleyp | last post by:
Hi all, I have a table (tblCodes) that has two fields: 'ID' and 'Code'. A small portion of the table is as follows: |-------------| | tblCodes | |-------------| | ID | Code |...
2
by: marco | last post by:
Dear List, as it seems, MS SQL as used in Access does not allow a select INTO within a UNION query. Also, it seems that a UNION query can not be used as a subquery. Maybe my (simplified)...
3
by: mikes | last post by:
I have 2 separate queries, which effectively are the same except they draw data from separate tables. Both tables are (design-wise) identical, only the data is different. for each query, there are...
5
by: BillCo | last post by:
I'm having a problem with a union query, two simple queries joined with a union statement. It's created in code based on parameters. Users were noticing some inconsistant data and when I analysed...
7
by: KoliPoki | last post by:
Hello every body. I have a small issue. Problem: I have a table with 4 descriptor columns (type). I need to formulate a query to retrieve a count for each type so I can group by...etc. The...
5
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big...
27
by: MLH | last post by:
How can I turn the following into a make-table query? SELECT & " " & AS Recipient FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID WHERE...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.