query with specific date from multiple tables

B

Bigmo2u

Registered User.
Local time
Today, 07:47
Joined
Nov 29, 2005
Messages
200
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?
 
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.
Click to expand...
I would think *everything* would be in one table with different ClaimID's. At the most, two tables with MasterClaims and ClaimDetails.
 
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.
 
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.
Click to expand...
 
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.
 
You are going to need to use AND rather than OR to narrow the selection.
 
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 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#));
 
RuralGuy said:
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.
Click to expand...
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.
 
I'm thinking it will look something like:
Code: 
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.
 
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.
 
Last edited:
Let's try GROUP BY and HAVING clauses:
Code: 
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?
 
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.
 
Last edited:
Well *that* didn't work. Let's go back to the WHERE clause but add paren's:
Code: 
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?
 
Last edited:
[Sent to Disbo] is a DateTime Field.

I got the same result without the paren's.
 
Last edited:
I have a strong feeling that [Sent to Disbo] is a Text field.
 
I wonder if you have to do a WHERE clause for *each* SELECT?
Code: 
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?
 
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?
 
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.
 
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.

:) :)
 
You must log in or register to reply here.

Similar threads

J
Date format mm/yyyy
Replies
17
Views
1,420
JPR
J
P
Extract Year From Control On Form
Replies
7
Views
450
arnelgp
arnelgp
D
Solved Date is displaying odd behavior
Replies
14
Views
844
Ken Sheridan
Ken Sheridan
E
Solved Union Query Average Grouped
Replies
5
Views
779
E9-Tech
E
Papa_Bear1
Access throws Error 3625 - Saying the text file [Import] specification doesn't exist, but it does.
2
Replies
37
Views
2,064
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom