First Appointment Of The Day For Each Employee (1 Viewer)

[shutzy]

hi guys, its been a while since i was last on here but i have run into some difficulty with a query

i would like to append some data to a tmp tbl.

i want to get the first appointment of the day for each employee. i have attached the relevant tables and a query that shows the information i need but it also shows more information. i have tried the nim function on the time and was successfull when i only had a few columns but when i added the colum [Items].[tblItems] it showed all the appointments for the day.

what i want to show is the first appointment for each employee.

can someone help please.

thank you

 

[namliam]

Are you looking for something like a simple "Group by" "min" query?

Straight copy/paste of the sql from your attached db:

SELECT tblEmployeeList.FirstName, tblOrders.OrderDate, Min(tblOrders.OrderTime) AS MinVanOrderTime
FROM (tblOrderItemsType INNER JOIN tblItems ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) INNER JOIN (tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblItems.ItemsID = tblOrdersItems.ItemsID
GROUP BY tblEmployeeList.FirstName, tblOrders.OrderDate, tblOrderItemsType.OrderItemsTypeID
HAVING (((tblOrders.OrderDate)=DateAdd('d',1,Date())) AND ((tblOrderItemsType.OrderItemsTypeID)=1));

 

[shutzy]

that is what i acheived but you have the extra OrderId in there. when i added that columnit listed more than just the results you got. all i need now is to add the column
Items.

i have added the sql below
[sql]
SELECT tblEmployeeList.FirstName, tblOrders.OrderDate, Min(tblOrders.OrderTime) AS MinVanOrderTime, tblItems.Items
FROM (tblOrderItemsType INNER JOIN tblItems ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) INNER JOIN (tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblItems.ItemsID = tblOrdersItems.ItemsID
GROUP BY tblEmployeeList.FirstName, tblOrders.OrderDate, tblOrderItemsType.OrderItemsTypeID, tblItems.Items
HAVING (((tblOrders.OrderDate)=DateAdd('d',1,Date())) AND ((tblOrderItemsType.OrderItemsTypeID)=1));
[/sql]

once i have the same results as you had but with this extra column it is complete. i do get confused with the ordering of the columns to get the right result.

 

[namliam]

All you need do is save this query as a new query and join it back to your original query on the KEY values and date and time....

Try using

 instead of [sql], works better :P

 

[shutzy]

the date and time are not unique to them. some days we have 4 employees and all could have something in at the same time. i really could do with OrderID to be able to have the unique relationship.

 

[namliam]

So you are saying that this wont work, but you havent really tried to follow what I said?

Take this query and save it as "qrynamliaM"

SELECT tblEmployeeList.EmployeeListID, tblEmployeeList.FirstName, tblOrders.OrderDate, Min(tblOrders.OrderTime) AS MinVanOrderTime
FROM (tblOrderItemsType INNER JOIN tblItems ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) INNER JOIN (tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblItems.ItemsID = tblOrdersItems.ItemsID
GROUP BY tblEmployeeList.EmployeeListID, tblEmployeeList.FirstName, tblOrders.OrderDate, tblOrderItemsType.OrderItemsTypeID
HAVING (((tblOrders.OrderDate)=DateAdd('d',1,Date())) AND ((tblOrderItemsType.OrderItemsTypeID)=1));

Now take this query

SELECT tblEmployeeList.FirstName, tblOrders.OrderDate, tblOrders.OrderTime, tblEmployeeList.MobileNumber, tblItems.Items
FROM (tblOrderItemsType INNER JOIN tblItems ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) INNER JOIN (tblEmployeeList INNER JOIN ((tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) INNER JOIN qryNamliam ON (tblOrders.OrderDate = qryNamliam.OrderDate) AND (tblOrders.OrderTime = qryNamliam.MinVanOrderTime)) ON (tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) AND (tblEmployeeList.EmployeeListID = qryNamliam.EmployeeListID)) ON tblItems.ItemsID = tblOrdersItems.ItemsID
WHERE (((tblOrderItemsType.OrderItemsTypeID)=1));

As your "New and improved" qryFirstAppointmentOfTheDay
given the fact that my query qrynamliaM gets the EmployeeID and date/time for the first appointment per employee... Joining those three back into your original query will return unique results regardless of 1,2,3 or 599 or 107.346 employees.

I do believe that that is what you are looking for .... or isnt it???

 

[shutzy]

i wasnt trying to argue. i was just trying to find the most definitive route of getting the unique record. there are variables that can happen that would cause an error in this. because it is not a unique record. yes those 3 factors date time employeeID can narrow the results down to one record in most cases but it is not fool proof.

the two queries as you know do bring th results i am wanting and i will just have to monitor them to make sure they always bring up the correct data.

im not saying you are wrong but this method does not use unique data.

 

[namliam]

It IS full and even fool proof, unless you have EmployeeListID's that are not unique.... However that is a KEY column thus should be unique data.

Only "issue" you may encounter is if any employee has more than one order at the same time, but then comes a discussion about data integrity and validity... But it will then return multiple orders, yes...
So, If you want the OrderID (*), you can simply add the OrderID in the "New and improved" qryFirstAppointmentOfTheDay,
you can add as many fields as you see fit... without losing the "first order of the day" requirement.

I suggest you try and break the query(s) by inputting data you expect will break it, either you will find you can break it and give a sample on the forum so the query can be fixed.... Or you will find you cant break it....

*) OrderID given it is a key field and autonumber, should not be used in any display or otherwize meaningfull form. It is a database thing that should remain hidden in the database inaccesable to the average user.
Key part of a Key is that it is and shall remain meaningless