Query If Equipment is InService

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
I have 2 Tables: 1 Contains by Date/Time Data and the other contains when equipment goes off of lease and we buy.

Trying to create 2 queries, one that will sum up items in lease, one that we bought.

Here are my tables:

First is the raw data:
Book1.xls
ABCD
1ID_TypeNameServiceTimeDate
21Test11/1/20057:51
32Test221/1/20058:51
41Test1.61/2/200513:00
51Test41/3/200515:25
Sheet1


This is the lease equipment table:
Book1.xls
GHIJ
1AutoIDID_TypeLeaseInServiceLeaseOutService
2111/1/20051/2/2005
3221/1/200512/31/9999
Sheet1


This is what the output should be for the leases table:
Book1.xls
ABCD
9output1
10ID_TypeNameServiceTimeDate
111Test11/1/20057:51
122Test221/1/20058:51
131Test1.61/2/200513:00
Sheet1


And this is what the equipment we own should look like:
Book1.xls
FGHI
9output2
10ID_TypeNameServiceTimeDate
111Test41/3/200515:25
Sheet1


I need help creating the date range parameters to accomplish this. I need to be able to query a subset using the Date/Time Field. IE if I just queried 1/1/05 I should only have items in lease.

Any ideas or help?

Thanks,
CT
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,437
Hi CT

I work with databases for equipment leasing but am struggling to understand your question. I have read and re-read your question and unfortunately I'm confused - I hope I am not trying to read too much into your question. :unsure:

To put your question in context, are you leasing equipment as the customer (i.e. the end user) or are you leasing the equipment as the owner of the equipment?

Also, what is the difference between the 3rd and 4th line items in your first sample such that the 3rd line item appears on the list of leases (per the 3rd sample spreadsheet) but the 4th line item does not? And based on the data you have provided, how did you determine that the 4th line item in the first sample spreadsheet means that you own the equipment per the last sample spreadsheet? (This makes me think you are looking at this from a customer perspective). I suspect the "ServiceTime" field is an activity code of some sort rather than a recorded value of time spent servicing the equipment - is this correct? If so, what are the codes? Lastly, am I correct in assuming that, per your sample data, that item 1 was leased twice, on consecutive days? Or am I completely mis-reading your sample data?

I have looked at your previous posts and I am none the wiser. Can you also provide some additional details as to the tables that you have and also what you want to achieve with this database? This appears to be a relatively new project for you (I could be wrong), and if it is initially set up correctly then it will save you many headaches (and questions on Mr Excel) later on.

Regards, Andrew. :)
 

CT Witter

MrExcel MVP
Joined
Jul 7, 2002
Messages
1,212
Andrew-

Thank you for your response. I have abandoned this project and given it to our IT SQL ppl to work on. Been 5 days and they can't get it to suit the business either. I guess the business process may have to be modified to suit data querying.

Thanks for your time and help!
CT
 

Forum statistics

Threads
1,147,848
Messages
5,743,525
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top