Trying to make form to check in and check out equipment

[rpeare]

How about this.



Your original example only had 2 data points so show me what the data looks like, show me what you WANT it to look like because your descriptions are not clear to me. If you can also provide a sample of the updated database that would be helpful.

[Marc76]

Last update today.
I got it to do mostly what I want. The Querry for locations is what I want it to look like. But I would like the Location.CompanyName (from Location Table), to appear, instead of LocationID.

I tried to upload the new copy of the DB. I dont know if it is or not. If so the Query for location should be populated with all 140 items with the other information I want, and a LOC ID. That Loc Id would preferrably be the LOC Company Name

[rpeare]

Make a new query with this SQL code:

SELECT  [EquipmentHistoryLocation Query].LocationID, [EquipmentHistoryLocation  Query].EquipmentID, [EquipmentHistoryLocation Query].CheckOutDate,  [EquipmentHistoryLocation Query].CheckInDate, Location.LocationID,  Location.LocationType, Location.LocationAddress2,  Location.LocationState, Location.JobNumber, Location.CompanyName
FROM  [EquipmentHistoryLocation Query] LEFT JOIN Location ON  [EquipmentHistoryLocation Query].LocationID =  Location.LocationID;

I don't see a query in here that is giving 140 results. The original query I gave you does return all of your equipment and their 'current' location based on the criteria I originally posted. You said:

Query for location should be populated with all 140 items with the other information I want

The only query you have with 'location' in the name is

EquipmentHistoryLocation Query

There are only 2 items in it, It is pulling information from your EQUIPMENT HISTORY table with no criteria, there are only 2 records in that table.

The original query I gave you DOES list all 140 pieces of equipment and their current location so what was the problem with that original query.

If I am not looking at your most updated copy of the database make a copy of the 'correct' one, give it a NEW NAME then zip it and upload it so there's no confusion since the last one you updated had the exact same zip file name.

[Marc76]

First off, thank you soooo much for your helping me. My brother referred to it as being a pilot and having to help someone land a plane who just took off in one.

When I run the first query you gave me it comes up with just the check IN/OUT date, and then the term you added for IIF clauses. No other information appears. I am at home now and stupid me forgot to save the DB to flash drive so I cannot paste the query I had so far. I, eventually, changing this and that got it to fill in all the things I wanted except the location information. What you just gave me is everything I want as far as location, now if the equipment ID, description and serial number can be added to that query it will be all I want. I have tried so hard and I get constant missing operator, or missing syntax errors. Its driving me nuts.

[rpeare]

in that same query I gave you in post #18 all you have to do is add your equipment table and link it through the equipment ID and pull in the fields you want.

[Marc76]

WHOOOPEEEEEEEEE, At long last it worked. Thank you to one and all for your help. Every tiny bit came together to get me what I was looking for. You are the best!

The resolution was in the MRCheck query i added the location table to it, so it contained historyID, EquipID, and LocID. then using what you gave me used this
SELECT qry_MRCheckout.EquipmentID, Equipment.EquipmentDescription, Equipment.InService, Equipment.BeingRepaired, Equipment.SerialNumber, EquipmentHistory.LocationID, Location.LocationAddress2, Location.LocationState, Location.CompanyName
FROM ((qry_MRCheckout LEFT JOIN EquipmentHistory ON qry_MRCheckout.EquipmentHistoryID = EquipmentHistory.EquipmentHistoryID) LEFT JOIN Equipment ON qry_MRCheckout.EquipmentID = Equipment.EquipmentID) INNER JOIN Location ON qry_MRCheckout.LocationID = Location.LocationID;


Which gave me all the information into one query i was looking for and now I can build my report for it.
Again thank you all!

Marc


Check This Solved!

[Marc76]

Um, I was looking around, and IIIIIIIIIIII don't see a solved button? I may just be blind, but can someone point it out to me?

[Marc76]

I was wrong. Now that I have the main prt working the first query is not doing what I want anymore

This privides all my equipment based on the highest history id. So it shows one entry for each item with it highest history id

SELECT EquipmentSubForm.EquipmentID, Max(EquipmentHistory.EquipmentHistoryID) AS EquipmentHistoryID
FROM EquipmentHistory LEFT JOIN EquipmentSubForm ON EquipmentHistory.EquipmentHistoryID = EquipmentSubForm.EquipmentHistoryId
GROUP BY EquipmentSubForm.EquipmentID;


When I add the Location ID to the list, it no longer only gives me the highest history ID, it lists all historyid entries.

SELECT EquipmentSubForm.EquipmentID, Max(EquipmentHistory.EquipmentHistoryID) AS EquipmentHistoryID, EquipmentHistory.LocationID
FROM EquipmentHistory LEFT JOIN EquipmentSubForm ON EquipmentHistory.EquipmentHistoryID = EquipmentSubForm.EquipmentHistoryId
GROUP BY EquipmentSubForm.EquipmentID, EquipmentHistory.LocationID;

So the "Max(EquipmentHistory.EquipmentHistoryID) AS EquipmentHistoryID" no longer seems to take precedent, and it just runs all data.

I'm thinkning its in the Group BY. But if I remove the EquipmentHistory.LocationID, it says there is no aggregate for LocationID.

I was so close too! I'm sure I still am, just something I'm missing, or have backwards maybe?

[rpeare]

You really haven't answered my my first query is not doing the job you want, but be that as it may without an updated copy of your database/queries I can't suggest anything.

If you upload a fresh copy just make sure it's named something different (the zip file) so we can distinguish it from your older file.

[Marc76]

Equipment Tracking.2.zip


I have a hard time explaining these things.

The first set of queries you gave me, the only thing i would get was a blank list with two entries, of those entries, the only reason i knew they were there was at the left of the screen was two dates, and two terms "check out".

no other data appeared. I don't know if it showed different on your computer but I never saw anything but those two items.

Im sure this is the latest save of the DB now.

under Queries you will see the MRCheckout. I did change some names to fit what I was looking for.

If you run the Query For Locations you will see the exact thing I wanted. Which is just a few alterations to the query you gave me.

But in qry_MRCheckout, you will see two entries for Equip ID # 2. Location ID 32, and 4., History ID 3 and 8. If you remove the Location ID from the qry, it will list the equipment by the highest history# so it will only show for #2 the history id 8 and location id 4, soon as you add the Loc ID it stops doing that and the history id 3 shows back up.

If you run the Report: Equipment Current Location, you will see the end game I desire. Only I just want the last history entry. Not every history entry.

[rpeare]

that was the whole point of the first query I gave you.

Your query is grouping by EQUIPMENTID *AND* LOCATIONID

in other words you're going to have a record for every piece of equipment for every location it went, that's not what you want.

Take that locationID out of the query qry_MRCheckout

My original query looked up the maximum check out date then linked that to the appropriate record because, if for any reason you enter enter a record for 1/2/2013 before you enter a record for 1/1/2013 your query will not show the correct information. But I will assume that will never happen :P

after you have removed the LOCATIONID from qry_MRCheckout

use this query instead:

SELECT qry_MRCheckout.EquipmentID, Equipment.EquipmentDescription, Equipment.InService, Equipment.BeingRepaired, Equipment.SerialNumber, EquipmentHistory.LocationID, Location.LocationAddress2, Location.LocationState, Location.CompanyName
FROM ((qry_MRCheckout LEFT JOIN EquipmentHistory ON qry_MRCheckout.EquipmentHistoryID = EquipmentHistory.EquipmentHistoryID) LEFT JOIN Equipment ON qry_MRCheckout.EquipmentID = Equipment.EquipmentID) LEFT JOIN Location ON EquipmentHistory.LocationID = Location.LocationID;

for your query for locations

[Marc76]

HAHAH, well now im fallowing you, but thats why I switched to the HistoryID because the I did not have it set up to put the check in date on the same line as the checkout. They would be two separate entries. I was in over my head enough to try and figure that one out and then set all the rest up to work with that.

I must be missing something. That did bring up the same results. However it is still pulling all entries instead of the latest entries.
HAHAHA

[rpeare]

this db is a mess your data isn't even correct to make this work, the reason you're getting more records than you have equipment is because you have no data in your EQUIPMENTID field in your EQUIPMENTHISTORY table.

Look at your equipment history table. There are no equipment ID's recorded.

On the existing records in the database you most recently attached put in an EQUIPMENTID of 2, 3 and 4 on each of the existing records

If you'd followed the method on my original suggestion it would work but I'm basically doing the exact same thing again here except I'm simply assuming that the most recent equipmenthistoryID is the most recent record which may not always be the case.

Save this:

SELECT EquipmentHistory.EquipmentID, Max(EquipmentHistory.EquipmentHistoryID) AS MaxID
FROM EquipmentHistory
GROUP BY EquipmentHistory.EquipmentID
HAVING (((EquipmentHistory.EquipmentID) Is Not Null And (EquipmentHistory.EquipmentID)<>0));

as qry_MRCheckout

SELECT Equipment.EquipmentID, Equipment.EquipmentMake, Equipment.EquipmentDescription, Equipment.InService, Equipment.BeingRepaired, Equipment.SerialNumber, Equipment.EquipmentModel, Location.LocationType, Location.LocationAddress2, Location.LocationState, Location.JobNumber, Location.CompanyName, IIf(IsNull([equipmenthistoryid]),"THERE IS NO HISTORY RECORD FOR THIS PIECE OF EQUIPMENT UPDATE YOUR EUQIPMENT HISTORY TABLE OR IT WILL NOT WORK",Null) AS WARNING
FROM Location RIGHT JOIN ((Equipment LEFT JOIN qry_MRCheckout ON Equipment.EquipmentID = qry_MRCheckout.EquipmentID) LEFT JOIN EquipmentHistory ON qry_MRCheckout.MaxID = EquipmentHistory.EquipmentHistoryID) ON Location.LocationID = EquipmentHistory.LocationID;

save this query as query for location

[Marc76]

So everytime I went looking for help on how to get Access to be able to record multiple items at one action, logged with a date, person etc. all I was ever told was Sub form sub form sub form.

The History Table would gather the date, the person and the location, and attach it to the subform via the history ID where multiple items could be inserted. I was told that Access is incapable of making a multi function singular form. So in order to do what I want I had to make a table for each item we have, a table for each location, a table for each person, link the to them all together as you see in the Relationships. It was the only way to get Access to update the History table with multiple items, to one date/person/location without doing one update action per item. If someone has a big job and takes 50 things he would have to perform 50 seperate transactions, unless there was a subform. So that it why there is no Equipment ID in the History form, it is linked via the Subform. I didnt believe it as I have had it before and I dont recall having to have sub forms. But no matter who I asked I was told, subform subform subform.

[rpeare]

we were talking about a query to show where the current item actually is. I didn't even look at your data entry forms. That was not part of your original post.

If you want to examine how to work your data structure I would have to have a good idea of what that actually entailed. For instance do you have a job or contract number,

are employees assigned to those jobs are contracts

are the tools assigned to the employees on the jobs or are they assigned to the job site

If you are trying to track a TOOL your main form should be based on your EQUIPMENT table. You would select the tool from an unbound combo box and have the MAIN FORM (let's call it EQUIPMENTSEARCH) where the EQUIPMENTID is the bound column and have the RECORD SOURCE of your form be something like:

SELECT * FROM EQUIPMENT WHERE [EQUIPMENTID] = [Forms]![EQUIPMENTCHECKIN]![EQUIPMENTSEARCH]

then whenever you exit the UNBOUND combo box requery the record (look at the ON EXIT property)

your SUBFORM should contain the equipment history, in other words when you requery your MAIN form will bring up the information relevant to the tool, your SUBFORM will bring up all the records of where that tool has been and the very last record (depending on how you sort it) will be where the tool currently is.