Linking Not working Tables - Queries Help

[Zonie32]

I have 2 tables/queries that I am trying to link to each other. One table is MasterTbl which has 13 fields. The other is SavingsTbl that has 5 fields.

The common fields for both tables are LOB, SIUDate and HIUDate. There is also a field in the master called OurBilledAmt and OurAllowedAmt.

So I import the MasterTbl fields each day from a file on our server, but the SavingsTbl is where I manually type in the savings amount we received from the company. (It’s always the same company and this is only done once a month). I type in the LOB, which for example is ICIAAA and the Their BilledAmt and Their AllowedAmt and also the SIUDate or HIUDate that it corresponds to. The dates are always one or the other, not both.

I ran a query off of the master table where it Groups By LOB, then counts Claims, sums OurBilledAmt, sums OurAllowedAmt and I have a date parameter using SIU Between [Start] and [End] OR HIU Between [Start] and [End].

That query works fine. Now I made another query based on the Savings table where I grouped by LOB, sums TheirBilledAmt and sums TheirAllowedAmt, also putting in the SIUDate and HIUDate parameter.

The problem is trying to link these 2 queries to where it shows LOB, OurBilledAmt, OurAllowedAmt, TheirBilledAmt, TheirAllowedAmt by the correct date parameters.

So it should look something like this after I enter the DateParameter of 2/1/04 and 2/29/04: I think the date parameter is messing me up. It keeps repeating the LOB column when I try it. I created a join where Take all from mastertbl and only those from savingstable where LOB is same. Not sure if that is right though.

LOB OurBill OurAlwd TheirBld TheirAl SIUDt HIUDt
ICIAAA 50.00 50.00 25.00 25.00 2/1/04
ICIBBB 100.00 100.00 50.00 50.00 2/2/04
ICICCC 200.00 200.00 50.00 50.00 2/5/04

 

[kubanvz]

I am not sure why you are experiencing this problem, but you might try the following: You should make a copy of the database "as is" first, and try these changes on a TEST or DEVELOPMENT copy of the DB, in case something blows up, of course.

Create a date table (DateTbl) with 2 fields, StartDate and EndDate. Input 2/1/04 in StartDate and 2/29/04 in EndDate. Then, bring DateTbl in your individual queries, and instead of paramters, use StartDate and EndDate from DateTbl in the Between criteria statement. I have done this type of thing on occasion and it seemed to have worked for me.