query with specific date from multiple tables

[RuralGuy]

So it *only* asked for the date once? Cool! Glad you got it working. Merry Christmas.

 

[neileg]

I know RuralGuy is valiantly trying to help you, but you're running into exactly the sort of proplem that stems from an un-normalised structure. You have one claim record in each of 40 tables when you should have 40 records in one table. I suspect your original table tried to use 40 columns. By getting the UNION query to work, this is simply a poor replication of what the design should be.

I know this sounds very negative, and I used to think normalisation was a load of rubbish, but it's not, it's the way that makes for a most efficient design.

 

[namliam]

Select * from (
      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;

Just stealing away... but this should work...

Seasons greetings from Amsterdam

The Mailman

 

[RuralGuy]

Merry Christmas from the good old US of A Mailman. If you look back in this thread you we see we had already tried your suggestion. It turns out that each SELECT statement stands alone so the WHERE clause *only* applied to the last SELECT.

 

[namliam]

I noticed that Rural Mailman... But not quite.... Compare the 2 find the difference and notice that mine DOES work... I am sure of it...

Greets

P.S. I did however not notice that the problem had been resolved allready, but still mine works (too). I just do not like the "Multi where-clause" thing

 

[RuralGuy]

I must have been sleeping. <slaps forehead>

 

[Bigmo2u]

I know RuralGuy is valiantly trying to help you, but you're running into exactly the sort of proplem that stems from an un-normalised structure. You have one claim record in each of 40 tables when you should have 40 records in one table. I suspect your original table tried to use 40 columns. By getting the UNION query to work, this is simply a poor replication of what the design should be.

I know this sounds very negative, and I used to think normalisation was a load of rubbish, but it's not, it's the way that makes for a most efficient design.

Believe me I am open to critizism on my DB cause this is the largest project I have taken on and I am a beginner with this stuff.

I don't have 1 claim in 40 tables (or I don't see it that way). I have master table that holds the MBR's info (SSN, Lname, Fname, unit, city, state and remarks)

Then the other tables are for claims that are submitted for specific dates (if the MBR is activated for 4 years they submit a claim every month for 48 months) all individual calims need to be tracked. With in those tables it holds ie dates of the claim, when it was received, payment amounts and rejections. If you can find a way to minimize my tables please help me. At this point I can use all the help I can get.

RuralGuy you did an outstanding job helping me.

 

[neileg]

Believe me I am open to critizism on my DB cause this is the largest project I have taken on and I am a beginner with this stuff.

I don't have 1 claim in 40 tables (or I don't see it that way). I have master table that holds the MBR's info (SSN, Lname, Fname, unit, city, state and remarks)

Then the other tables are for claims that are submitted for specific dates (if the MBR is activated for 4 years they submit a claim every month for 48 months) all individual calims need to be tracked. With in those tables it holds ie dates of the claim, when it was received, payment amounts and rejections. If you can find a way to minimize my tables please help me. At this point I can use all the help I can get.

I can't see any justification for having separate tables that are date specific. Perhaps I'm missing something? If you hold the claim records with a field for this date in one table, then you can group them by date, by their reference in the master table, or whatever. The separate table approach is like a spreadsheet not a relational database. You must have to add a table for each time period, whereas with one table, you just roll on ad infinitum.

 

[Bigmo2u]

Tables are set up like this:

tbl_mbr_info (only one table)

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 thru tbl_partial_48

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

I don't see where the partials can be rolled into one table. All the data that is in the table is specific to 1 claim and not all 48 claims that they may be submit.

If you could help minimize my tables I would greatly appreciate it.

I am not trying to be defensive, just trying to understand how to accomplish this task. I wish my company would send me to an access class to help me understand how to do this more efficiently.

 

[reclusivemonkey]

tbl_partial_1 thru tbl_partial_48

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

I don't see where the partials can be rolled into one table. All the data that is in the table is specific to 1 claim and not all 48 claims that they may be submit.

You've totally missed the point there. Its not the data; its the fields. All the fields in your tables 1-48 are the same. Simply add another field for the 1-48 part and there you are. Seriously, if you don't get your tables right, the bigger your DB grows, the bigger the mess you will end up in.

 

[Bigmo2u]

I know what you are guys are saying and I just don't know how to do it. Like I have said I am a beginner and I have no formal or informal training on DB's. Any thing you guys can help me on is greatly appreciated.

RuralGuy - I am trying to put in this in to capture the dates of all the claims received for a specific date range:

SELECT SSN, [Claim Received] FROM [M00091 Partial 1 Claims]
WHERE [Claim Received] = Between [Type the beginning date MM/DD/YYYY] And [Type the ending date MM/DD/YYYY]
UNION SELECT SSN, [Claim Received] FROM [M00091 Partial 2 Claims]
WHERE [Claim Received] = Between [Type the beginning date MM/DD/YYYY] And [Type the ending date MM/DD/YYYY]

this is the error I am getting:

Syntax error (missing operator) in query expression '[Claim Received] = Between [Type the beginning date MM/DD/YYYY] And [Type the ending date MM/DD/YYYY]'

any ideas?

 

[RuralGuy]

Leave off the "=" sign! Just:
WHERE [Claim Received] Between [Type the beginning date MM/DD/YYYY] And [Type the ending date MM/DD/YYYY]

 

[Bigmo2u]

Duh, sometime I don't think. Thanks RuralGuy. You are a life saver.

 

[RuralGuy]

Glad to assist.

 

[namliam]

Not to whine or anything but dabbling like this by (again no offence meant) amatures in access is what is giving us "profesional" developers a bad name.

I totaly agree with the Rural Mailman on this comment:
"You've totally missed the point there. Its not the data; its the fields. All the fields in your tables 1-48 are the same. Simply add another field for the 1-48 part and there you are. Seriously, if you don't get your tables right, the bigger your DB grows, the bigger the mess you will end up in."

I appologize up front if this post seems to be somewhat hurtfull or disrespectfull or anything. It is absolutly not meant that way, it is "just an observation"

Regards

The Mailman

 

[Bigmo2u]

Not to whine or anything but dabbling like this by (again no offence meant) amatures in access is what is giving us "profesional" developers a bad name.

I totaly agree with the Rural Mailman on this comment:
"You've totally missed the point there. Its not the data; its the fields. All the fields in your tables 1-48 are the same. Simply add another field for the 1-48 part and there you are. Seriously, if you don't get your tables right, the bigger your DB grows, the bigger the mess you will end up in."

I appologize up front if this post seems to be somewhat hurtfull or disrespectfull or anything. It is absolutly not meant that way, it is "just an observation"

Regards

The Mailman

No Offense taken Mailman. I understand what you are saying but can't figure out how to do it. Yes Tables 1- 48 have the same data fields, But how do i capture and hold 48 separate claims with 48 separate dates, 48 separate audits, 48 saperate prevals and 48 separate payments (Granted there are more fields than this to capture) "on the same person".

tbl_Partial _1 is for the MBR's claim that covers 11/1 to 11/30 then next month the MBR submits another claim for 12/1 to 12/31 that goes in tbl_partial_2 so on and so on. All the fields are the same in every table because the data is captured for historical purposes per claim and data can not be replace every month when the new claim comes in.

if there are any suggestions on how to limit the number of tables I have I would greatly appreciate it.

 

[reclusivemonkey]

I totaly agree with the Rural Mailman on this comment:
"You've totally missed the point there. Its not the data; its the fields. All the fields in your tables 1-48 are the same. Simply add another field for the 1-48 part and there you are. Seriously, if you don't get your tables right, the bigger your DB grows, the bigger the mess you will end up in."

That comment was made by me, not Rural Guy. I don't think that anyone would take a single person using Access as representative of all Access Users; as a wise man once said

A poor workman blames his tools.

and as I say

A poor manager believes him.

 

[namliam]

That comment was made by me, not Rural Guy.

My appologies for the oversight

I don't think that anyone would take a single person using Access as representative of all Access Users;

Slightly disagree... It is as it should be, but still ... One should not steel either and still that happens. We as profesionals get frowned upon a lot (lets be honest) and personaly I blaim the leags of amatures.

In my 8 years as a profesional I have had projects handed down to me from my (new) customers. More often than not I had to "suggest" to them to chuck the half project that they had going allready for half a year. When they did, for me to finish it within 3 months.

as a wise man once said

A poor workman blames his tools.

Totaly agree...

and as I say

A poor manager believes him.

Agree whole hartedly, unfornatly in personal experience I must say there are more poor managers than there are good (let alone outstanding) managers out there.

Greets

The mailman