I'm trying to write a query for a query table in Excel, and not having much luck. Help appreciated, and apologies if I've posted this in the wrong forum.

I've got two tables. Item_In records the details (serial, part, date of arrival etc) of items arriving. Item_Action records the details (serial, part, date of processing etc) of what's been done to each item.

What I want is to create a query which will join Item_In and Item_Action, get all instances where the serial field in Item_In is not Null And Item_Action is Null (i.e. where an item has been booked in, but not had anything done to it) and from there use the Item_In date to return an average length of time item have been hanging around awaiting processing.

Is this possible in MS Query, and if so, does anyone know how do do it?

Hi
Just pondering over the tables and relationship with these two tables..
Q: Can any one item have more than one action against it..?

Yes, they can. There's a simplified table layout below, if it helps.

Item_In
UniqueID | Serial | Part | Bookin_date | Bookin Time


Item_Action
UniqueID | Action | Serial | Part | Action_Date | Action_Time


Each item may have multiple actions recorded against it.

I've also realised that each serialised item could be booked in multiple times. It would be an outer join on the most recent unique record of each serial number in each table.

This is getting more complicated by the minute...

Hi
Going to go on a limb and suggest this could be a M:M type scenario and I'll explain why..


It's this statement that leads me to suggesting it..

Each item can have many actions..
Each action can be assigned to many items..

Example:
tblItem >> jtblItemAction << tblAction

Would this be right..?

( I don't see the need to be storing both Serial/Part twice..)

Okay... let me make sure I understand this.

A M:M relationship between the two tables would result in a table that looks like this:
(the items are desktop PCs BTW)

Serial | Action
0001 | Wipe
0002 | Wipe
0003 | Wipe
0003 | Install
0003 | NULL
0004 | Install
0005 | NULL
0006 | NULL

My plan was to pull out anything with a NULL value for Action, use the serial to get the Bookin_Date, and do Count/Average on the date to get some stats back. In this case 0003,0005 & 0006.

The problem here is that 0003 has been booked in, gone out, and come back again. I need to find a way of preventing the most recent instance of this serial on the Item_In table being matched with an action which occurred before it was originally booked in. Which suggests making the Book_In serials UNIQUE or otherwise by retrieving only the most recent action for a serial from the Item_Action table... does this seem reasonable or am I barking up the wrong tree here?

Hi
Yep.. that looks like a proper structure, in the junction table you could add other fields that give you more detail about the item. I would also include a date/time stamp so to help you find the most recent item date.
If you like we could create a small mockup of the tables and see how your queries would be created and see how they run..

So... I reckon the junction table needs to look like

Item_In.Serial, Item_Action.Action, Item_In.Bookin_date, Item_In.Bookin_time


which i think would be created by doing



It's the outer join which is giving me problems. I've got the MS Kb article on making joins in MS Query in front of me, but even copying their examples verbatim and making appropriate changes is getting me nowhere...

I said in the OP that I wanted to get an average length of time an item waited for an action. Thinking about it, I'll save a lot of time and aggro if I try and get details of all of them, and dump them in a pivottable... it'll make reporting easier in the long run.

I've got a subtract query working, which pulls back all items on the Item_in tabLE (SIGL$) which don't have matching serials on the Item_Action table (SRN$).




Now checking how it'll behave when duplicate serials/actions appear on the tables...

It doesn't. Need to check dates too, as I said before. Not enough sleep last night...

I think what I need is to compare the dates on the Item_In (SIGL$) and Item_Action (SRN$) and return any where the date on the Item_In is later than the date of last action; e.g. If it has an action date of the 12th, and a Bookin dat of the 25th, it's gone out, come back and is awaiting another action.


I tried adding an OR

WHERE (`SRN$`.Serial Is Null OR `SIGL$`.`Bookin-Date > `SRN$`.Date)

but MS Query treats `SRN$`.Date as a parameter, and prompts me for a value. Does anyone know any way I can get this to get the comparator from the table it's already got?

Hi
You really shouldn`t use those characters in your field names..
Example:
The following symbols must not be used as part of a field name or as part of an object name: .
/
*
:
!
#
&
-
?
;
"
'
$


List of reserved words in Access


Edited by: Larry Larsen on Wed Aug 8 4:33:53 EDT 2007.

Hi
I was looking to calculate the time difference between actions.. using a MS GetElapsedTime function.
eg:

The use of '$' in my table names isn't a matter of choice. The tables I'm querying are sheets in an Excel file, and MS Query treats the sheet/table names as parameters unless the '$' is appended. I'd like a proper database, but apparently there isn't budget for that...

Finally got completely hacked off with MS Query. I put together the following query for querying the file using ADO/Jet and it seems to work.

SELECT [SIGL$].[Serial], [SIGL$].[Bookin-Date]

FROM [SIGL$] LEFT OUTER JOIN [SRN$] ON [SRN$].[Serial] = [SIGL$].[Serial]

WHERE [SRN$].[Serial] IS Null OR [SIGL$].[Bookin-date] > [SRN$].[Date];

Seem reasonable?