Between Two Dates - Different Table

[CodenameAter]

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.

In regards to these three queries. They don't look anything like the first one you created. Like I said before, I need to find a way to change the database structure as I have so many more things I need to do, this being one of the easier queries I need to complete.

Just to give you an idea... I need to do pattern analysis, I need to manipulate blocks if females laid an egg the next morning... I need to analyze chain events at each antenna... may the list go on. If there is a program out there that would be easier to use and would be able to handle something as basic as date/time (like I said, I can do all of this in excel except for pattern analysis it just can't handle the data size I am working with without crashing as well).

[June7]

Yes, different queries. Tried to get away from domain aggregate functions which can be slow performers.

[CodenameAter]

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.

Just to make sure we are on the same page... Because I have a list of dates on one table and a bunch of events with timestamps on another... Access does not determine if events fall between the list of dates. I only ask this because if what I want to do cannot be done in Access, I am going to scrap it all together and try some of the other suggested databases above. However, if there is something I can do with the current relationships that would improve this, I would prefer to stick with Access.

[June7]

It can be done as I have demonstrated. The problem is performance with large datasets. It is not the date/time data type that is the issue. Performance issue with DLookup or Cartesian relationship would manifest with text or number range data as well. Dates are actually stored as double number. Access just 'knows' to present the values of a date/time field in a date format.

[CodenameAter]

It can be done as I have demonstrated. The problem is performance with large datasets. It is not the date/time data type that is the issue. Performance issue with DLookup or Cartesian relationship would manifest with text or number range data as well. Dates are actually stored as double number. Access just 'knows' to present the values of a date/time field in a date format.

I apologize but you are losing me ... moral of the story, Access cannot handle my request and therefore I need to search for a new database?

As a side note, my actual database is approximately 57x larger than the sample I provided you.

[June7]

I don't know that having another db app (Oracle, SQLServer, SQLServerExpress, etc) to store data will improve performance issue. AFAIK, those apps don't have built-in user interface and programming (macros and VBA are Microsoft Office tools). Would need a frontend that links to the tables which could be Access, VB.Net, Visual Basic, etc.

And installing and setting up those db apps is not simple, as compared to installing Access. I tried SQLServerExpress once and gave up. Oracle and SQLServer are VERY expensive. SQLServerExpress is a freebie but I think limited to 10 GB.

I have never built db with other software. I know SQLServer and SQLServerExpress can build query objects and that there is some way for Access to see those queries and pull records from them. However, you still have the issue of how to lookup up the BlockID. I don't know how other apps can do that. I don't know if they have equivalent to DLookup() function. Might have to be the Cartesian relationship query.

If there are other all-in-one apps like Access, I am not familiar with them but I doubt they would perform any better for this situation.

The real issue is lack of foreign key field in the imported data for the BlockID so it can directly link to the BlockID primary key field in BlockDates table.

[CodenameAter]

So my understanding is that a foreign key in the imported data would be something that the BlockID relates to. Obviously I can just add a field called BlockID and leave it empty in the upload field, but I imagine that would not solve the issue at hand.

It is so strange that excel can do this so easily, see the attached table I created in excel. The only issue with this table has so many formulas with so many data lines that it takes forever just to update the formulas.

[June7]

Concept of a Relational Database is datasets have primary and foreign keys so that they can be related. Without those keys, things get difficult and performance is impacted, as you are discovering. This concept is common to all relational database software.

[CodenameAter]

Ya, I guess I assumed that date could be used to relate two tables, but that appears to not be the case here

Anyway, thanks again for all the help! I really appreciate everything you have done on trying to help me out!!

[June7]

A date could be used to relate datasets. Issue in this case is that in one table the dates are a range, not a single key value. Dates are not the key is this situation, the BlockID is the key, which is missing in the data import. Attempts to use the date range to find the appropriate BlockID key faced performance issue.

There may be ways to populate the foreign key BlockID field in the imported dataset by running UPDATE queries on small subsets of records. This could be automated with VBA code.

Is the imported data cumulative (meaning each time you import it brings in all records, old and new) or is it just new data that needs to be appended to the table?

[CodenameAter]

What do you mean, one table the dates are a range? In both tables, I either use two separate fields for dates.

[June7]

Sorry, yes both tables had ranges but only Arrive field from uploaded data was used from that table.

The date range I meant was [Start] and [Start] & ' 11:59:50 PM' - this is the date range of interest pulled from the BlockDates table (which originally had Start and End fields). The DLookup attempted to locate the BlockID where Arrive field from the uploaded data was between those range dates.