Query If Equipment is InService

[CT Witter]

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
	A	B	C	D
1	ID_Type	Name	ServiceTime	Date
2	1	Test	1	1/1/20057:51
3	2	Test2	2	1/1/20058:51
4	1	Test	1.6	1/2/200513:00
5	1	Test	4	1/3/200515:25
Sheet1

This is the lease equipment table:

Book1.xls
	G	H	I	J
1	AutoID	ID_Type	LeaseInService	LeaseOutService
2	1	1	1/1/2005	1/2/2005
3	2	2	1/1/2005	12/31/9999
Sheet1

This is what the output should be for the leases table:

Book1.xls
	A	B	C	D
9	output1
10	ID_Type	Name	ServiceTime	Date
11	1	Test	1	1/1/20057:51
12	2	Test2	2	1/1/20058:51
13	1	Test	1.6	1/2/200513:00
Sheet1

And this is what the equipment we own should look like:

Book1.xls
	F	G	H	I
9	output2
10	ID_Type	Name	ServiceTime	Date
11	1	Test	4	1/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

 

[Andrew Fergus]

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.

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]

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