Access 2003, Union query...or Not

[Dubblosephen]

I have two UNRELATED tables...Custom ers and Vehicles...

I need to make a query for when an existing customer (in customers table) needs a loaner car for a vehicle in our inventory (in vehicles table) There is no relationship between the customer or vehicle, so how can I apply this.

I tried the following:

select vehicle.[VIN]
from vehicle
where (((Vehicle.[VIN,C,30]) Like "*" & [Search for] & "*"))
UNION
select customer.[lastn]
where (((Customer.[lastn]) Like "*" & [Enter Customers Last Name] & "*"));

The first "where" prompts the user to enter a partial VIN and successfully returns the correct vehicle when run alone.
The second "where" prompts for which customer to use by entering a partial last name, which returns a single customer when run alone.

I will eventually need their name, address, DL#, which all fields are in the Customer table and understand I need the same number of tables and they must be the same type, to which they are. I just used a few fields to see what I am doing wrong.

So, when I run this, it would show the VIN only...I need to see the customers last name also - this is where I'm lost.

Over all I need one query for a form to list the vehicle info and customer info, which is in the DB, but not related.

[jforbes]

You've done a good job at figuring out what you want to display, but I wouldn't rely on a single query to give you those results. You could get this to work, but it will be clunky and with just a bit off effort you could have an elegant solution.

What I would do...

Create a Datasheet Style Form for your Vehicle Table

Create a Datasheet Style Form for your Customers Table

Create a Single Form with Two unbound fields at the Top:

1. txtVIN
2. txtCustomer

Then add both of the recently created Datasheet Style Forms as SubForms, right on top of each other.

The fun part starts by adding code to both AfterUpdate events of txtVIN and txtCustomer. The code should create a string for the Filter clause and apply the Filter to the respective Datasheet SubForm. i.e. When your user enters in a VIN, the txtVin_AfterUpd ate will fire, creating a where clause, and apply it to the Vehicle SubForm:

Dim sSQL as String
sSQL = "VIN LIKE '*' & Me.txtVin.Value & '*'"
Me.subVehicle.Form.Filter = sSQL 
Me.subVehicle.Form.FilterOn = True
Me.subVehicle.Visible = True
Me.subCustomer.Visible = False

Lastly, show just the subForm related to the data that was searched.

The End result will be a Form where the user picks what they are looking for, by entering in either the VIN or Customer Name. If they enter a VIN, only Vehicle information will be displayed, if they enter a Customer Name, only the Customer information will be displayed.

[Dubblosephen]

I apologize for not relaying why I need this, the info would overlay a scanned form in a report for a loaner vehicle. Both the customer info and vehicle info would always need to be displayed. By the description, specifically the "OR", I don't think this would work.

I literally just thought of having two reports in one, or a subreport....ca n I have two queries in one report with a subreport? Hopefully this won't open a wormhole...?

Main or report 1 - select customer_stuff from customers;
subreport or report 2 - select VIN from vehicles;

I'm going to try this now, unless you are saying your recommendation will always return both the vehicle and customer and I just misunderstood.

[PhilOfWalton]

I believe you want a join table similar to this:-

TblJoinVehicleCustomer
VehicleID         Long    Joint Primary Key
CustomerID        Long    Joint Primary Key
DateLoaned        Date
DateReturned      Date
 

Set appropriate relationships, and you have a record of who borrowed each vehicle and when and correspondingly who each vehicle was loaned to.

Phil

[Dubblosephen]

I cannot join. This is an existing application that I am linking to the tables via Access. Besides, no join should exist as this is vehicles in inventory that a customer may be temporarily assigned as a loaner. I never know in advance which vehicle a customer would be assigned.

There is no way to have two tables where I can select a record from table 1 (Vehicle) and a record from table 2 (Customer) and have the appropriate fields and link to the report...I can't imagine this hasn't been requested before.

Long story short, we (I) created a significant number of reports for us including State forms that auto fill using overlays to a scanned form. We literally print and sign deals, no handwriting. changes we requested from the vendor typically are not 100% of our request or use a really poor 10pt terminal font. So not professional looking. Making the reports/forms ourselves we have the flexibility to make them as professionally looking as we want.

With that said, it's not something I can request from the vendor, at least for free anyways. They typically only do things if its for the greater good, not so much a change for one customer, understandably.

((Thinking out loud...))
I will try to do something using make table which can append to a record, first for the vehicle, then the customer. Since its two-ish times a week, I can create the make table, assign record id numbers as the join and then delete the record after its printed. Since we have a signed copy, I do not need historical records. It will always be a 1 to 1 join with 1 record in each table.

[jforbes]

Is this for a Form or a Report? Your first post is about a Form, you subsequent posts seem to be about Reports. There are different approaches depending on which target you are going against.

[Dubblosephen]

I was mistaken, this definitely needs to be a report, not form. At a minimum needs to prompt for a customer and VIN since there is no relationship.

[jforbes]

If you don't want to build a Table like Phil's TblJoinVehicleC ustomer to link your records together for reporting, then I would create a Form that Launches your Report. The Report Launcher Form would prompt for both the CustomerID and VehicleID and when both are supplied, it would enable the Button that launches the Report. On the Report, I would put two different SubReports, one for the Customer, the other for the Vehicle. The Source for each SubForm would be a Query that refers back to the Launcher Forms CustomerID and VehicleID.

I would not attempt a Union or Join into one Query as that will just complicate things and make maintenance more difficult.