Between Two Dates - Different Table

[CodenameAter]

Just thought I would add this in:



Both Arrive, Block_Start and Block_End are all dates according to Access. I am using the following Expression.

Block: IIf([Arrive] Between [BlockDates]![Block_Start] And [BlockDates]![Block_End],[BlockDates]![BlockID],"No ID")

It returns "No ID" even though I can see that there are times within Block_Start and Block_End in the Uploaded_Scrub table.

So annoying!

[June7]

Looking at Blocked_Scrub(****) query. This expression Format([Start],"dd-mmm-yyyy") & " 11:59:59 PM" results in data like 11:59:59 PM so no results meet the IIf() criteria. I pulled [Start] field into the query and every record is empty, in spite of their display in the Merged_BlockInfo query.

Why does the query have a link between Arrive and Start fields? Since these data include time parts, how can records ever match? Removing the link causes error and query won't open.

See if this gets what you want:

SELECT Uploaded_Scrub.RFID, Band_IDs.[Band], Uploaded_Scrub.LocID, Uploaded_Scrub.Arrive, Uploaded_Scrub.Leave, DateDiff("s",[Arrive],[Leave])/86400 AS Visit_Length, DLookUp("BlockID","Merge_BlockInfo","#" & [Arrive] & "# Between [Start] And Format([Start],'dd-mmm-yyyy') & ' 11:59:59 PM'") AS Block
FROM Band_IDs RIGHT JOIN Uploaded_Scrub ON Band_IDs.[Aviary_RFID] = Uploaded_Scrub.RFID
ORDER BY Uploaded_Scrub.Arrive;

[CodenameAter]

I'm a bit confused. They are both time/date formats. I want BlockIDs returned if the Arrive time falls between the Block Start and End time.

This is only step 1 in about 10 things I need to do with this database, is Access simply unable to lookup to see if times fall between times in a different table? I can do this really easy in excel, it just can't handle the amount of data I have.

[CodenameAter]

Also I ran the SQL script you gave me above and it crashed the database

[June7]

The query works in the db you posted. However, domain aggregate functions are known to perform slowly in large datasets.

Yes, both are date/time formats and they will link but apparently none are an exact match down to the second therefore the Start field is empty.

[CodenameAter]

Okay! Thanks for letting me know & writing the code.

Maybe you could recommend a database tool out there that could handle these kind of requests... something easy to use but would be able to handle larger data loads. The sample I sent you is 1/10 of what I am working with. Once the project roles on in full, I am going to be working with data hundreds of times larger than the sample. I think I had 1.7k data lines, the raw data coming in down the road will be in the millions of data lines.

[June7]

I don't think tool is the issue - bad data structure is. Oracle, SQLServer, SQLExpress can store more data and Visual Basic or VB.Net can be frontends, but the query issue is the same.

[CodenameAter]

Okay, I am a little confused. When you say bad data structure, what do you mean?

Are you referring to the titles of the tables? The data format I am uploading it in? All of these things can be changed quite easily on my end if that would improve things.

[June7]

I am talking about the lack of a direct relationship between these datasets that is causing last resort use of DLookup to pull an identifier value.

Only alternative I can think of involves Cartesian relation of the datasets - this is a query with no JOIN clause so every record of each table joins with every record of other table.

Query1
SELECT BlockDates.BlockID, BlockDates.[Trial #], BlockDates.[Day #], BlockDates.Block_Start, BlockDates.Comments, BlockDates.Experiment_Name, Uploaded_Scrub.RFID, Uploaded_Scrub.LocID, Uploaded_Scrub.Data1, Uploaded_Scrub.Data2, Uploaded_Scrub.Arrive, Uploaded_Scrub.Leave
FROM Uploaded_Scrub, BlockDates
WHERE (((Uploaded_Scrub.Arrive) Between [Block_Start] And CDate(Format([Block_Start],"dd/mm/yyyy") & " 11:59:59 PM")));

Query2
SELECT Query1.*, Band_IDs.[Band], Band_IDs.Aviary
FROM Band_IDs RIGHT JOIN Query1 ON Band_IDs.Aviary_RFID = Query1.RFID;

All in one
SELECT Query1.*, Band_IDs.[Band], Band_IDs.Aviary
FROM Band_IDs RIGHT JOIN (SELECT BlockDates.BlockID, BlockDates.[Trial #], BlockDates.[Day #], BlockDates.Block_Start, BlockDates.Comments, BlockDates.Experiment_Name, Uploaded_Scrub.RFID, Uploaded_Scrub.LocID, Uploaded_Scrub.Data1, Uploaded_Scrub.Data2, Uploaded_Scrub.Arrive, Uploaded_Scrub.Leave
FROM Uploaded_Scrub, BlockDates
WHERE (((Uploaded_Scrub.Arrive) Between [Block_Start] And CDate(Format([Block_Start],"dd/mm/yyyy") & " 11:59:59 PM")))) AS Query1 ON Band_IDs.Aviary_RFID = Query1.RFID;

Does that get anything like what you want? Does it crash - it certainly might.

[CodenameAter]

I will give these a shot but what do you mean by direct relationships. Are you referring to start time and end time?

Also, don't worry about writing more SQL code. Simply writing me one line of SQL code isn't going to solve the issue since I need to do about 35 calculations in total similar to this and I barely understand what you are writing. I was hoping to use the builder to make my life more simple.

[June7]

I am talking about a primary/foreign key association.

You can use the builder. That's what I did to generate the SQL statements. I used the db you posted. Build query in designer and switch to SQL View to see the statement. Copy/paste the statements into SQL View then switch to designer.

[CodenameAter]

Okay, I am going to read up on Primary vs Foreign key associations and see if I can find a way to provide these relationships to see if that helps. Thank you.

[June7]

That's what the DLookup (as well as the suggested Cartesian query) is attempting to do. BlockID is defined as primary key in BlockDates but those values are not available as foreign key in the Uploaded data. The DLookup attempts to pull the correct BlockID values to associate with Uploaded records. The DLookup will pull the first BlockID for each record that meets the search criteria. If there is more than one possible match, the others are ignored. However, the Cartesian relationship should show all matches.

[CodenameAter]

BlockID is a primary key and it is unique as every experiment has a unique BlockID that represent the experiment name, trial# and day#. And you are correct, Uploaded Data does not have BlockID nor Block Dates because it is raw dating coming in; this cannot be changed.

This goes back to my original point, it sounds like Access can't easily handle dates & times. So my question for you, how do I change the data structure within the current limitations in order to improve these queries? Or, is there software out there that would be easier for me to work with that won't constantly crash or lag when running these queries?

[June7]

I don't think the data type (date/time) is the issue. It would probably manifest with text or number type as well.

No, I don't know any other methods.