Billing Database Help 1

[JStrittmatter]

I have a billing database. I have a consumer table consumer service table and a receivable table.

There is a date field in both consumer service andreceivable tables both dates are formatted 3/15/2004.

What I am trying to do is a query to give me a list of those consumer who do not have service this but that have an outstanding balance over the fiscal year July 1 to June 30.

The tables are all linked by a consumer id

Consumer Table
BSSID
First Name
Last Name
Address
etc...
Consumer Service
BSSID
Date (12/1/2004)
Trips
Fare
Receivable
BSSID
Date (12/15/2004)
Amount
Check Number

If anyone can get me pointed in the right direction I would really appreciate it.

 

[lespaul]

You're missing a key word in here to clue us in to what you are looking for:

What I am trying to do is a query to give me a list of those consumer who do not have service this ???? but that have an outstanding balance over the fiscal year July 1 to June 30.

Leslie

 

[JStrittmatter]

The missing keyword is Month

 

[lespaul]

Ok, let's see if we can get this in a single query, if not, you may have to create two queries that gather parts of the information you need and then use those queries to finish it up. So try this first, if it doesn't work, come back and we'll do it the other way:

SELECT BSSID, FirstName, LastName
FROM Consumer
WHERE BSSID IN (SELECT DISTINCT BSSID FROM Consumer Service WHERE BSSID NOT IN ([COLOR=red]SELECT BSSID FROM Consumer Service WHERE [Date] BETWEEN #06/01/2004# AND #06/30/2004# [/color]) AND BSSID IN ([COLOR=blue]SELECT DISTINCT BSSID FROM Receivables WHERE [Date] BETWEEN #07/01/2003# AND #06/30/2004# HAVING SUM(AMOUNT) > 0[/color])

If you have problems try, running the red query and see if that works alone, then run the blue query and see if that works alone.

Another suggestion, change your date field names. DATE is a key/reserved word, you should name it something meaningful like ServiceDate and ReceivableDate.

Leslie

 

[lespaul]

oops, add [ ] around each 'Consumer Service'

Leslie

 

[JStrittmatter]

Thanks Leslie

You pointed me in the right direction and I was able to tweak it from there.

 

[lespaul]

no problem! glad you got it working

leslie