List of dates and records with no matching record OR existing record with higher date (1 Viewer)

[diberlee]

Hi,

I've been asked to get some information from my database and I'm a bit stuck.

I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field

My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.

So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)

I can manage a query that looks at a certain date that it prompts for on each run:

SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));

I would like a query that lists all dates in a range, and shows the same information for each day listed. Anybody able to offer some assistance? I'm leaving work soon, but will reply as soon as possible if more info is needed

Regards
Duane

 

[plog]

Can you provide sample data to demonstrate what you hope to accomplish. I'm going to need 2 sets:

A. sample starting data from all tables. Include table and field names. Include enough sample data to cover all cases.

B. sample expected result data based on A. Based on the initial data you provide, demonstrate what your query should return.

 

[diberlee]

Can you provide sample data to demonstrate what you hope to accomplish. I'm going to need 2 sets:

A. sample starting data from all tables. Include table and field names. Include enough sample data to cover all cases.

B. sample expected result data based on A. Based on the initial data you provide, demonstrate what your query should return.

I certainly can. I'll need some time to prepare something that I can send though as the information held is sensitive. I'll write back here later this evening or tomorrow.

Regards

 

[diberlee]

Hi Plog,

Please find attached a zip containing a pared down version of my data and an Excel sheet showing how I would do what I'm after in Excel and what results I would expect (Sheet4)

So to clarify the first thing we do with an entry in tbl_main is save one of 3 outcomes. We're looking for a query that lists all dates in a range and shows the number of entries that had been received on this date, but hadn't had an outcome saved. We also want the value of these entries.

Ideally the query would provide an output that contained the info in columns A, F and G on the Excel sheet.

The tickboxes in tbl_main indicate the outcome. I know it's bad practice to double up on data in a database in this way, but as a bit of a newbie I've found they help a lot in querying and reporting.

Thanks in advance
Duane

 

[plog]

I'm unable to open the database, could you save it as a .mdb and repost?

 

[diberlee]

Sorry about that, here you go

Cheers

 

[plog]

The issue is your table structure. You shouldn't store data values in your table nor field names. A good way to tell if you are doing this is tables with identical structures.

All of the data in tlk_located, tlk_bulk and tlk_unableToLocate should be in the same table. Instead of storing the type (bulk, located, unable to locate) in the table name as you are now, it should go into a new field--OutcomeType. That means your new table which combines these 3 tables should look like this (I've included the first 2 records of each table as sample data):

tlk_Outcomes
OutcomePK, trust2FK, OutcomeTime, OutcomeType
1, 13, 9/1/2014 8:26:00 AM, U
2, 14, 9/1/2014 8:28:00 AM, U
3, 2, 9/1/2014 8:04:00 AM, L
4, 3, 9/1/2014 8:06:00 AM, L
5, 1, 9/1/2014 8:00:00 AM, B
6, 20, 9/20/2014 12:00:00 PM, B


Get your data into that structure and your query becomes trivial.

 

[diberlee]

The issue is your table structure. You shouldn't store data values in your table nor field names. A good way to tell if you are doing this is tables with identical structures.

All of the data in tlk_located, tlk_bulk and tlk_unableToLocate should be in the same table. Instead of storing the type (bulk, located, unable to locate) in the table name as you are now, it should go into a new field--OutcomeType. That means your new table which combines these 3 tables should look like this (I've included the first 2 records of each table as sample data):

tlk_Outcomes
OutcomePK, trust2FK, OutcomeTime, OutcomeType
1, 13, 9/1/2014 8:26:00 AM, U
2, 14, 9/1/2014 8:28:00 AM, U
3, 2, 9/1/2014 8:04:00 AM, L
4, 3, 9/1/2014 8:06:00 AM, L
5, 1, 9/1/2014 8:00:00 AM, B
6, 20, 9/20/2014 12:00:00 PM, B


Get your data into that structure and your query becomes trivial.

Now that you've said that I'm kicking myself... I'll look at switching things round to all be in the same table.

I originally set it up with the 3 tables thinking that it would ease the load on the database as the work involved in following up each category is split between a team. I thought at the time that pulling records from 3 different tables would mean everything ran faster. I don't think there's much of a speed benefit in practice though. Would you agree?

Cheers
Duane

 

[plog]

So many people try and solve problems that don't exist and actually create ones with their effort. You can run it both ways and see if there's a difference, but my guess is that there wouldn't be a discernable difference if one existed at all.

 

[diberlee]

So many people try and solve problems that don't exist and actually create ones with their effort. You can run it both ways and see if there's a difference, but my guess is that there wouldn't be a discernable difference if one existed at all.

Can't argue with that. I'm a sucker for an imaginary problem!

Thanks for your help on this