query with specific date from multiple tables

[Bigmo2u]

I am trying to pull just a specific date that a Claim was sent to be processed. But the query has 40 tables that it needs to go through to get all possible dates. I know you will all say this is over kill on the tables, but each table represent a travel claim.

The problem I have is that I get to the 10th claim and it stops pulling the date. I put the following statement in the criteria:

[Type the beginning date MM/DD/YYYY]

any ideas?

 

[RuralGuy]

You are really going to have to give us more to work with. How about posting the SQL or even the code that executes it too. As to:

I know you will all say this is over kill on the tables, but each table represent a travel claim.

I would think *everything* would be in one table with different ClaimID's. At the most, two tables with MasterClaims and ClaimDetails.

 

[Bigmo2u]

The reason I have 40 + tables is that a person submits a claim every month for up to 4 years and I have to capture the all the info. I ran into limitation on one table before that is why I split them apart. The Person's SSN links all the info together. I would send you the mess I have created but it has SSN in it. This is my 1st stab at Access and I have had a real hard time at it.

Table are set up like this:

tbl_mbr_info

SSN
LName
FName
Unit
City
State
Remarks
POC
POC email

The following is data that is being captured per partial for historical data over the next 4 years.

tbl_partial_1

SSN
Claim received
MBR sig Date
Clerk Init recv
DN Date
Disbo DN Date
Partial Paid date
sent to disbo date
partial dov#
partial amount
partial complete
endorsement missing
missing signature
1351 incomplete
missing orders
missing appn
itinerary Endos conflict
missing receipts
endorsement incomplete
start date
end date
pre-val date
pre-val clerk
drop dead date

Maybe someone can help me limit the number of tables I have but I don't see how that is possible.

 

[RuralGuy]

OK, that takes care of the defensive comments. How about responding to this part:

You are really going to have to give us more to work with. How about posting the SQL or even the code that executes it too.

 

[Bigmo2u]

I hope this helps.

SELECT [MBR info].SSN, [MBR info].LName, [MBR info].FName, [MBR info].Unit, [M00091 Partial 1 Claims].[Sent to Disbo], [M00091 Partial 1 Claims].[Start Date], [M00091 Partial 1 Claims].[End Date], [M00091 Partial 2 Claims].[Sent to Disbo], [M00091 Partial 2 Claims].[Start Date], [M00091 Partial 2 Claims].[End Date], [M00091 Partial 3 Claims].[Sent to Disbo], [M00091 Partial 3 Claims].[Start Date], [M00091 Partial 3 Claims].[End Date], [M00091 Partial 4 Claims].[Sent to Disbo], [M00091 Partial 4 Claims].[Start Date], [M00091 Partial 4 Claims].[End Date], [M00091 Partial 5 Claims].[Sent to Disbo], [M00091 Partial 5 Claims].[Start Date], [M00091 Partial 5 Claims].[End Date], [M00091 Partial 6 Claims].[Sent to Disbo], [M00091 Partial 6 Claims].[Start Date], [M00091 Partial 6 Claims].[End Date], [M00091 Partial 7 Claims].[Sent to Disbo], [M00091 Partial 7 Claims].[Start Date], [M00091 Partial 7 Claims].[End Date], [M00091 Partial 8 Claims].[Sent to Disbo], [M00091 Partial 8 Claims].[Start Date], [M00091 Partial 8 Claims].[End Date], [M00091 Partial 9 Claims].[Sent to Disbo], [M00091 Partial 9 Claims].[Start Date], [M00091 Partial 9 Claims].[End Date]
FROM (((((((([MBR info] LEFT JOIN [M00091 Partial 2 Claims] ON [MBR info].SSN = [M00091 Partial 2 Claims].SSN) LEFT JOIN [M00091 Partial 3 Claims] ON [MBR info].SSN = [M00091 Partial 3 Claims].SSN) LEFT JOIN [M00091 Partial 4 Claims] ON [MBR info].SSN = [M00091 Partial 4 Claims].SSN) LEFT JOIN [M00091 Partial 5 Claims] ON [MBR info].SSN = [M00091 Partial 5 Claims].SSN) LEFT JOIN [M00091 Partial 6 Claims] ON [MBR info].SSN = [M00091 Partial 6 Claims].SSN) LEFT JOIN [M00091 Partial 7 Claims] ON [MBR info].SSN = [M00091 Partial 7 Claims].SSN) LEFT JOIN [M00091 Partial 8 Claims] ON [MBR info].SSN = [M00091 Partial 8 Claims].SSN) LEFT JOIN [M00091 Partial 9 Claims] ON [MBR info].SSN = [M00091 Partial 9 Claims].SSN) LEFT JOIN [M00091 Partial 1 Claims] ON [MBR info].SSN = [M00091 Partial 1 Claims].SSN
WHERE ((([M00091 Partial 1 Claims].[Sent to Disbo])=#12/15/2005#)) OR ((([M00091 Partial 2 Claims].[Sent to Disbo])=#12/15/2005#)) OR ((([M00091 Partial 3 Claims].[Sent to Disbo])=#12/15/2005#)) OR ((([M00091 Partial 4 Claims].[Sent to Disbo])=#12/15/2005#)) OR ((([M00091 Partial 5 Claims].[Sent to Disbo])=#12/15/2005#)) OR ((([M00091 Partial 6 Claims].[Sent to Disbo])=#12/15/2005#)) OR ((([M00091 Partial 7 Claims].[Sent to Disbo])=#12/15/2005#)) OR ((([M00091 Partial 8 Claims].[Sent to Disbo])=#12/15/2005#)) OR ((([M00091 Partial 9 Claims].[Sent to Disbo])=#12/15/2005#));

What I want is just claims for 12/15/2005 for example or a way of just telling what date I want [Type the date MM/DD/YYYY] . If I change the 'OR' to 'AND' would that give me the Just the dates of what I asked for and nothing more? Cause right now I am getting the all previous claims that are associated with the claims on 15th. (meaning if a guys has claim on the 15th of this month it will pull all claim before that as well.

 

[RuralGuy]

You are going to need to use AND rather than OR to narrow the selection.

 

[RuralGuy]

On second thought, I don't think that will work either. Have you tried a UNION query? It would simplify the query and make selection of the record you need easier to locate.

 

[Bigmo2u]

I used AND instead of OR and now it returns me nothing. Any idea what I am doing wrong?

WHERE ((([M00091 Partial 1 Claims].[Sent to Disbo])=#12/15/2005#)) AND ((([M00091 Partial 2 Claims].[Sent to Disbo])=#12/15/2005#)) AND ((([M00091 Partial 3 Claims].[Sent to Disbo])=#12/15/2005#)) AND ((([M00091 Partial 4 Claims].[Sent to Disbo])=#12/15/2005#)) AND ((([M00091 Partial 5 Claims].[Sent to Disbo])=#12/15/2005#)) AND ((([M00091 Partial 6 Claims].[Sent to Disbo])=#12/15/2005#)) AND ((([M00091 Partial 7 Claims].[Sent to Disbo])=#12/15/2005#)) AND ((([M00091 Partial 8 Claims].[Sent to Disbo])=#12/15/2005#)) AND ((([M00091 Partial 9 Claims].[Sent to Disbo])=#12/15/2005#));

 

[Bigmo2u]

On second thought, I don't think that will work either. Have you tried a UNION query? It would simplify the query and make selection of the record you need easier to locate.

I looked that up in the Access Bible and looks to be a perfect fit for what I want to do. The only problem I have is I don't know how to write that SQL statement to make it what I want. I will try and see what I come up with and I will post what I wrote.

 

[RuralGuy]

I'm thinking it will look something like:

SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 2 Claims] 
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 3 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 4 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 5 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 6 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 7 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 8 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 9 Claims]
WHERE [Sent to Disbo] = #12/15/2005#
ORDER BY SSN;

Then you can join this query with the [MBR info] query/table on SSN if you need a name on each record.

 

[Bigmo2u]

Sent to Disbo____Start Date________End Date

________________10/1/2005________10/31/2005
12/16/2005_______9/1/2005_________9/30/2005
12/19/2005_______7/5/2005_________8/12/2005
12/19/2005_______8/12/2005________8/15/2005
11/23/2005_______10/1/2005_______10/31/2005
12/12/2005_______11/1/2005_______11/30/2005

It's not pulling just the date of 12/15/2005 for sent to disbo.

 

[RuralGuy]

Let's try GROUP BY and HAVING clauses:

SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 2 Claims] 
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 3 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 4 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 5 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 6 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 7 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 8 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 9 Claims]
GROUP BY SSN
HAVING [Sent to Disbo] = #12/15/2005#;

I'm working with my <<<Air Code>>>
What does *your* SQL actually look like?

 

[Bigmo2u]

I am running exactly like you set it up.

SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 2 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 3 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 4 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 5 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 6 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 7 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 8 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 9 Claims]
GROUP BY SSN
HAVING [Sent to Disbo] = #12/15/2005#;

Error:

You tried to execute a query that does not include the specified expression 'Sent to Disbo' as part of an aggregate function.


RuralGuy - You are awsome and doing a great job helping me. Thank you so much.


I have found a longer way to do it. I do a query on every partial that looks like this:

SELECT [M00091 Partial 1 Claims].SSN, [M00091 Partial 1 Claims].[Sent to Disbo], [M00091 Partial 1 Claims].[Start Date], [M00091 Partial 1 Claims].[End Date]
FROM [M00091 Partial 1 Claims]
WHERE ((([M00091 Partial 1 Claims].[Sent to Disbo])=#12/15/2005#));

Then I set the Union query like you orginal set up that looks like this:

SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [P1 SD]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [P2 SD]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [P3 SD]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [P4 SD]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [P5 SD]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [P6 SD]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [P7 SD]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [P8 SD]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [P9 SD]
WHERE [Sent to Disbo] = [Enter Date mm/dd/yyyy]
ORDER BY SSN;

Result:

Sent to Disbo_____Start Date_______End Date
12/15/2005_______7/31/2005_______11/26/2005
12/15/2005_______11/1/2005_______11/30/2005
12/15/2005_______11/1/2005_______11/30/2005
12/15/2005_______11/1/2005_______11/30/2005
12/15/2005_______11/1/2005_______11/30/2005
12/15/2005_______11/1/2005_______11/30/2005

The only problem with doing it this way is that this report will be run daily and require changes be made to a lot of queries.

 

[RuralGuy]

Well *that* didn't work. Let's go back to the WHERE clause but add paren's:

SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 2 Claims] 
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 3 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 4 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 5 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 6 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 7 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 8 Claims]
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 9 Claims]
WHERE (([Sent to Disbo]) = #12/15/2005#)
ORDER BY SSN;

It is more out of superstition than anything else! I don't understand why the WHERE clause isn't working.

[Sent to Disbo] *is* a DateTime field isn't it?

 

[Bigmo2u]

[Sent to Disbo] is a DateTime Field.

I got the same result without the paren's.

 

[RuralGuy]

I have a strong feeling that [Sent to Disbo] is a Text field.

 

[RuralGuy]

I wonder if you have to do a WHERE clause for *each* SELECT?

SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM [M00091 Partial 2 Claims]
WHERE [Sent to Disbo] = #12/15/2005#
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 3 Claims]
WHERE [Sent to Disbo] = #12/15/2005#
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 4 Claims]
WHERE [Sent to Disbo] = #12/15/2005#
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 5 Claims]
WHERE [Sent to Disbo] = #12/15/2005#
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 6 Claims]
WHERE [Sent to Disbo] = #12/15/2005#
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 7 Claims]
WHERE [Sent to Disbo] = #12/15/2005#
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 8 Claims]
WHERE [Sent to Disbo] = #12/15/2005#
UNION SELECT SSN, [Sent to Disbo], [Start Date], [End Date] FROM  [M00091 Partial 9 Claims]
WHERE [Sent to Disbo] = #12/15/2005#
ORDER BY SSN;

Can we try it as a test?

 

[Bigmo2u]

You did it RuralGuy. You are the best....... The [Sent to Disbo] is a Date/Time field.

I can not say thank you enough.......

Would it do it if I put this statement in:

WHERE [Sent to Disbo] = [Enter Date mm/dd/yyyy]

instead of

WHERE [Sent to Disbo] = #12/15/2005#

Would that work or would it only do it for the 1st partial?

 

[RuralGuy]

I think it will ask every time but you can try it. I'm certain you could reference a function that returned the date you needed to look for. It might even be able to use the Forms collection to pull the date from a control on a form.

So the WHERE for *every* SELECT worked correct? Thanks for the test. That means *every* SELECT stands on it's own. A useful thing to know. Post back if you need any more help with this. I like to see problems through to their conclusions.

 

[Bigmo2u]

I put my statement in there and I get the same result. You saved The Marines here a lot of Hours doing this manually. Thank you for your support.

The other way that you are talking about capturing the date I am lost on. I have no idea how to do that.

Thank you very much for seeing this problem through to the end.