Joining Tables

[Rules2liveby]

Hi All - Fairly new to ADPm, but hoping you can help.

Using Access 2003 and SQL backend.

I am trying to complete a call-logging database and have the following tables:

CLIENT (ID, name, address, telephone etc)
ITEM (ID, ClientID, itemtype, itemdesc etc)
CALLLOG (ID, ItemID, description, date, priotity etc)
CALLLOGENTRY (ID, CallLogID, Date, Time, Desc, activity etc)

My main Call Log form:

I have a page which allows a user to select a client from a dropbox, then the item from the list. Then you can add the other details to the table CALLLOG. Another tab with subform allows access to the CALLLOGENTRY table.

It is this first page which is causing me the problem. The SQL I am using is as follows:

SELECT MSItem.Make, MSItem.Model,
MSItem.SerialNumber,
MSSLA.SLADescription,
MSClient.Name, MSItem.Location,
MSCallLog.*

FROM MSCallLog
INNER JOIN
MSItem ON MSCallLog.ItemID = MSItem.ItemID
INNER JOIN
MSSLA ON MSItem.SLAID = MSSLA.SLAID
INNER JOIN
MSClient ON MSItem.ClientID = MSClient.ClientID

------------------------

Problem is that the form works with the ITEM as the main table - so for example if I try to delete a CallLog, it actually deletes all CallLogs for the current Item.

Second problem is the order is not fixed, I would like to ORDER BY CallLogID, but if I do this, although the page works, when I try to access the page through a link (CallLogID = CallLogID) i get a Column not found error.

I'm going round in circles, any help would be much appreciated.

Thanks,
Patrick

 

[Rules2liveby]

Forgot to mention - SLAID is as follows - just a lookup table:

SLAID (ID, desc)

e.g. 1, "4 Hour Response"