Question not sure how to accomplish this

[animatedjay]

What i have is this... there is a table of devices. 4000 laptops, desktops, printers etc. this table has the device serial number, Asset Tag, and a check box for Out on Loan (plus other device info, like make, model, location, etc.) Then there is another table for device that are Out On Loan. I have to keep the tables separate so i can report on a history of the same devices that were loaned out. if i had it all on the same table, that would only tell me the last time the device was loaned out. Hence the 2 tables.... anyway. For the out on loan table i have the device asset tag field, the loaned start date, the loaned end date and the Out on loan check box again (not tied to the devices table). The tech enters (on the out on loan table via a form) the device asset tag, and the start date. the out on loan checkbox's default value is set to true. when the device is returned the tech enters in the loaned end date and unchecks the out on loan checkbox. i need to have this out on loan checkbox from the out on loan table reflect the out on loan check box from the devices table. I have tried to tie the tables together via relationships and/or queries but without success. please help. Thank you. and keep in mind that this is a web database and i can only use marco's not vba and some macros are not available (see the attachment of ones i can use)

 

[plog]

You shouldn't store data that can be calculated. Specifically, you shouldn't have any 'Out On Loan' fields, because you can look into the Out On Loan table and determine which items have been loaned but not returned.

Create a query based on your Out On Loan table, bring in Asset Tag, Loan Start Date and Loan End Date. Underneath the Loan End Date Field put in 'Null' and run the query. This will give you all items currently loaned out. Save this query and use it whenever you need to determine which items are loaned out.

 

[animatedjay]

I have that and it works. i have created a query based on my out on loan table. the query has DeviceAssetTag, LoanStartDate, LoanerInfo, LoanEndDate and an out on loan checkbox. The query is setup to display those records that the out on loan checkbox is true. i can change that to display only those records that the lease end date is null as you suggest. but the real question is how can i reflect that on another table. how can i tell another table to say the device is out on loan if either the check box is unchecked or the date is no longer null?

 

[plog]

First, you need to get rid of the checkboxes. Since you can derive this data from existing data, you don't need to store it seperately. Second, when you need to know if an item is out on loan, you bring that query you just created into another query along with whichever other table's data you need.

 

[animatedjay]

If i need to get rid of the check boxes, how will it flag that the device is out for repair? can you give an example?

 

[plog]

I would accomplish this in the loan table by essentially loaning the computer to the repair department. It would then have a loaned out start date and a blank loaned out end date and you could use the method I previously described to ascertain that the item was loaned out and where.

 

[animatedjay]

Can i attach the database so you can see what i'm doing wrong?

 

[animatedjay]

Here is what i have, however this only works locally on my laptop. When i publish this to the sharepoint server it either limits the dropdown box to the first 400 numbers (i have over 4000) or if i change the combo box to a text field it does not allow me to add a line via the data sheet. I tried to come up with a for to add a new entry but it still has the same limits.

 

[plog]

To see if an item is out on loan you would create a query using this SQL:

SELECT [Device Asset Tag] FROM OutOnLoan WHERE IsNull([Loaned End Date])=TRUE;

There is no need to 'flag' a record in the Devices table because you are essentially doing that when you enter data into the OutOnLoan table.

Similarily, you would modify your OutOnLoan_Query by removing the [Out On Loan] field from it and placing 'Is Null' in the criteria area underneath the [Loaned End Date] field.

 

[animatedjay]

I can only use macros on a web database. And i'm not sure how to create your query code you have listed.(sorry, I do not have the full understanding of all this like i need to have.)

How do i indicate that the item is out on loan in the Device data tab? How can i tie that to the out on loan table?