Query with multiple output???

[boberca]

Hello,
I got the following table and I need to figure out how to create query that will allow me to check: what car or limousine were rented, what car or limo available and when rented vehicles are expected to get back.

I appreciate any assistance in advance. I'm complete newbie to Access

ID Type of car Car/Limousine Rented Start Date Days Rented End Date
1 Sport 2006 BMW M3 convertible 01/12/2007 3 04/12/2006
2 Sport 2005 Mercedes SLR 20/12/2006 1 21/12/2006
3 Sport 2007 Infinity G35 sedan 07/12/2006 1 08/12/2006
4 Sport 2006 Mustang GT convertible 08/12/2006 2 10/12/2006
5 Sport 2007 Audi TT convertible 21/12/2006 2 23/12/2006
6 Sport 2007 Nissan 350Z convertible 19/12/2006 2 21/12/2006
7 Sport 2006 Ferrari 360 spyder convertible 15/12/2006 1 16/12/2006
8 Sport 2007 Porsche 911 twin turbo convertible 14/12/2006 3 17/12/2006
9 Sport 2007 Porsche 911 twin turbo convertible 17/12/2006 2 19/12/2006
10 Sport 2007 Infinity G35 sedan 15/12/2006 1 16/12/2006
11 Sport 2006 BMW M3 convertible 06/12/2006 2 08/12/2006
12 Sport 2007 Audi TT convertible 09/12/2006 1 10/12/2006
13 Sport 2007 Porsche 911 twin turbo convertible 03/12/2006 3 06/12/2006
14 Sport 2006 Ferrari 360 spyder convertible 06/12/2006 2 08/12/2006
15 Sport 2006 Ferrari 360 spyder convertible 10/12/2006 1 11/12/2006
16 Sport 2006 Mustang GT convertible 22/12/2006 2 24/12/2006
17 Sport 2007 Infinity G35 sedan 29/12/2006 1 29/12/2006
18 Sport 2007 Porsche 911 twin turbo convertible 10/12/2006 3
19 Sport 2005 Mercedes SLR 03/12/2006 1
20 Sport 2005 Mercedes SLR 01/12/2006 1
21 Sport 2007 Nissan 350Z convertible 09/12/2006 2
22 Sport 2006 Mustang GT convertible 04/12/2006 2
23 Sport 2007 Porsche 911 twin turbo convertible 21/12/2006 2
24 Sport 2007 Nissan 350Z convertible 06/12/2006 1
25 Sport 2005 Mercedes SLR 15/12/2006 1
26 SUV 2007 Porsche Cayenne S 12/12/2006 2
27 SUV 2007 Acura MDX 05/12/2006 1
28 SUV 2007 Porsche Cayenne S 31/12/2006 2
29 SUV 2007 Acura MDX 01/12/2006 3
30 SUV 2007 Porsche Cayenne S 17/12/2006 1
31 SUV 2007 Infiniti QX56 11/12/2006 2
32 SUV 2007 Infiniti QX56 09/12/2006 1
33 SUV 2007 Lexus RX 400h 04/12/2006 1
34 SUV 2007 Lexus RX 400h 28/12/2006 2
35 SUV 2007 Land Rover LR3 15/12/2006 2
36 SUV 2007 Cadillac Escalade 15/12/2006 1
37 SUV 2007 Infiniti QX56 05/12/2006 2
38 SUV 2007 Cadillac Escalade 25/12/2006 2
39 SUV 2007 Porsche Cayenne S 03/12/2006 1
40 SUV 2007 Land Rover LR3 31/12/2006 1
41 Limousine 2006 Mercedes stretch limousine 01/12/2006 2
42 Limousine 2006 Cadillac stretch limousine 01/12/2006 2
43 Limousine 2006 Chevrolet Corvette stretch limousine 05/12/2006 1
44 Limousine 2006 BMW M5 stretch limousine 09/12/2006 1
45 Limousine 2006 Cadillac stretch limousine 15/12/2006 1
46 Limousine 2007 Bus Limousine 12/12/2006 2
47 Limousine 2007 Bus Limousine 02/12/2006 3
48 Limousine 2006 BMW M5 stretch limousine 15/12/2006 1
49 Limousine 2006 Chevrolet Corvette stretch limousine 20/12/2006 1
50 Limousine 2006 Mercedes stretch limousine 18/12/2006 2

 

[dhookom]

How far have you gotten? Did you try create a query based on the table? Are the results going to be presented to the users in a form or in a report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[boberca]

Well, I didn't get far. I tried to create query based on the table. I have 3 columns: Type of car, Start date and End date, however I don't know how to establish proper criteria. I would like to have results presented as a report.

Thank you!

 

[dhookom]

I would start by creating a form with unbound controls where users can either enter or select criteria values. You can then create a report and a button on the form to open the report. You can find samples of code by searching google for

Hookom strWhere = "1 = 1 "

Come back if you can't find or don't understand the samples.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[boberca]

Ok. I got the the unbound form done, however I still can't grasp how is it going to help me with identifing what car or limousine were rented, what car or limo available and when rented vehicles are expected to get back.

In addition, when I'm creating the report it doesns't ask me for the any inputs, so I guess my query is not doing anything.

 

[dhookom]

Please ask one question at a time. Then as that question gets answered, you can move on to another. Each of your posts have asked at least 3 unique questions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[boberca]

Ok. Than the question is:
Providing info given in post #1 how do I identfy what car or limousine were rented??

Thanks

 

[dhookom]

Add two text boxes to a form for date value entry and name them txtStart and txtEnd. Let's assume your report is named "rptRentals". Use the the command button wizard to add a command button to open your report. Then modify the code like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
   strWhere = strWhere & " And [End Date] >=#" & _
      Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
   strWhere = strWhere & " And [Start Date] <=#" & _
      Me.txtEnd & "# "
  Else
   strWhere = strWhere & " And [Start Date] <=#" & _
      Me.txtStart & "# "
End If
DoCmd.OpenReport "rptRentals", acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[boberca]

I probably very dumb. Still can't get it to work. I created form like youi told me here:

http://i163.photobucket.com/albums/t286/boberca/Screen02.jpg

Than modified code and now I'm getting this

http://i163.photobucket.com/albums/t286/boberca/Screen05.jpg

I can upload db if you are willing to look at it and tell me what am I doing wrong.

Thank you

 

[dhookom]

Which line is causing the error? I'm not sure you understand how to name a control. It looks like you changed the Caption property of a label to the name you should have given the text box.

Is your report named rptRentals?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]