Access2000: complex query problem

[Arvi Laanemets]

Hi

When designing phone bills processing database, I need a little help. I
explain my problem step-by-step.

At start I have 3 tables:
Bills:
BillID; Month; Provider; PhoneNum; ServiceID; CallTime; CallLength; Sum;
TargetNum

Services:
ServiceID; Service; TargetCountry; NetID

TargetNets:
NetID; TargetNet

The relations are Bills.ServiceID->Services.ServiceID and
Services.NetID->TargetNets.NetID

Based on those 3 tables I create a query:
SELECT Bills.BillID, Bills.Month, Bills.Provider, Bills.PhoneNum,
Services.Service, Bills.CallTime, Bills.CallLength, Bills.Sum,
Bills.TargetNum, Services.TargetCountry, TargetNets.TargetNet FROM
(TargetNets RIGHT JOIN Bills ON TargetNets.NetID = Bills.NetID) RIGHT JOIN
Bills ON Services.ServiceID = Bills.ServiceID;

So long it's all OK. Now I have 2 additional tables

NumTypesX:
ID; PhoneNum; NumTypeID, FromDate

NumTypes:
NumTypeID; NumType

The relation between those tables is
NumTypesX.NumTypeID->NumTypes.NumTypeID. There is no direct relationship to
table Bills estabilished.

To every PhoneNum in table Bills is given NumType, which can change. Which
NumType is valid for which date is estabilished in table NumTypesX. What
I'm searching for, is a way do have for every record in query according
NumType displayed.
I have designed an UDF, which returns any value from a table, for which
certain field is equal to some value, and for which a date field is nearest
to >= to some value - i.e. it can return according ID or NumTypeID from
table NumTypeX into query. But I can't find a way to implement it into any
RIGHT JOIN clause, so that right NumType is returned. I.e. I have working
queries:

SELECT Bills.BillID, Bills.Month, Bills.Provider, Bills.PhoneNum,
Services.Service, Bills.CallTime, Bills.CallLength, Bills.Sum,
Bills.TargetNum, Services.TargetCountry, TargetNets.TargetNet,
MyFunction(...) As ID FROM (TargetNets RIGHT JOIN Bills ON TargetNets.NetID
= Bills.NetID) RIGHT JOIN Bills ON Services.ServiceID = Bills.ServiceID;

, or

SELECT Bills.BillID, Bills.Month, Bills.Provider, Bills.PhoneNum,
Services.Service, Bills.CallTime, Bills.CallLength, Bills.Sum,
Bills.TargetNum, Services.TargetCountry, TargetNets.TargetNet,
MyFunction(...) As NumTypeID FROM (TargetNets RIGHT JOIN Bills ON
TargetNets.NetID = Bills.NetID) RIGHT JOIN Bills ON Services.ServiceID =
Bills.ServiceID;

, but I need an according NumTypes.NumType as last column instead!


Thanks in advance for any help!

 

[Tom Ellison]

Dear Arvi:

You mention you have a UDF. I take it then you have an MSDE / SQL Server
database for this. If you would just post a script of this database, I
could readily replicate your situation and work on a solution. Would this
be acceptable? You can just post it here.

If that is too public, I could accept that as an email attachment.

Tom Ellison

 

[Arvi Laanemets]

Hi

It's a simple Access database at moment - and an unsplit one. I don't think
there will be many people using it, so Access will do at start (but I alvays
can convert it to SQL database when needed). In general it is meaned to
analyze calls: p.e. to find ot for which users using mobiles instead of
common phones is prefereble, or which users have misused company phones over
certain limit, etc. I.e. a work for a person responsible for such stuff. As
an additional bonus, the database will allow to follow, which phones/numbers
to which users are attached - so another possible user from here.

General information about project:
Phone bill is read form provider's site as csv-file. As this csv-files needs
some converting to make it usable, and as I find it simpler to use Excel to
manipulate csv-files, then this part is done in Excel. User select a
csv-file, and the information from it is split into 3 tables - Bill,
PhoneNumbers and Services (old data are overwritten - so the Excel file
contains info about bill from certain provider for certain month)
Those 3 Excel tables (for provider) are linked to Access database. Database
contains its own Bills, PhoneNumbers and Services tables, where info about
all bills from all providers is collected - a procedure adds data from
according linked Excel table into Bills table, when there is no data for
this month available in Access table, and adds any phone numbers or
services, which were missing in Access tables before.
Then there are some tables, kept in project itself - mainly various
registers like for phone number types, users, user classes, departments,
etc. And some tables, where p.e. phones/phone numbers or users are connected
to those registers - all such connections have a 'Start time', and are valid
from start time to today, or until an entry with next 'Start time' for this
phone number or user is estabilished.

At moment I'm designing the interface. There will be a form, where user can
view all detailed call's data together with related information (this form
will be based on query, my question was about) and apply various filters to
it, and forms to edit registers and to connect phone numbers with users,
number types, etc. And some specific reports, like the list of users, who
have calls to mobile numbers over some limit, and don't have mobile, or the
list of users, which have both common phone and mobile, didn't use his limit
for mobile calls, and at same time did make calls from his common phone to
mobile number over some limit, etc.

About my question: I think I find a solution. I'll save the query where
ID-fields from connection tables are returned through UDF's. And then I use
it as source for another query, where those ID-fields are replaced with
according values from registers.


Arvi Laanemets

Dear Arvi:

You mention you have a UDF. I take it then you have an MSDE / SQL Server
database for this. If you would just post a script of this database, I
could readily replicate your situation and work on a solution. Would this
be acceptable? You can just post it here.

If that is too public, I could accept that as an email attachment.

Tom Ellison