Best way to compare data from two months in same table

[tagteam]

I am trying to figure out the best way to see the difference between car rental in two months.


For example: Car rental to a customer in January and February. I need to see if there are any cars in January that are not there in February. I also need to see if there are any cars in February that are not in January. I also need to see if their is a price difference between the cars.

I have tried several methods but they are all kind of messy. Ideally I would see just the cars that are different or maybe see them highlighted but It seems excel may be better for this task because all the queries I run seem to end up being a bit confusing. The problem is all the data is in one table, so I can make a copy of the table or I can maybe create two temporary tables. I am not really sure. Currently I am trying both methods.

I also have an analysis form that has two subforms. On the left are all the cars that were billed out in the previous month and on the right all the cars that were billed out in the current month. This gives me the car details but does not make it easy to see where the difference is. For example, if there is one less car on the right it is not easy to find if a user has hundreds of cars.

The cars are on what is called a rider. So a customer may have 3 riders with 12 cars on each rider. So I have a button on the analysis form that will open another form and show me two more tables with the total number of cars for each rider, the total rent for the rider and then some unbound fields that add that stuff up to quickly show if the Number of riders is the same, the number of cars is the same, and the total is the same.

The problem is that a car may be replaced. So car number 1 is wrecked and gets replaced by car 37 for the same amount. This means that the amount will be the same, the total number of cars will be the same, and the number of cars in the rider will be the same but there will be a new car in the rider and an old one will be gone. Trying to quickly pinpoint this difference is where I am getting stuck.

Does anyone have any recommendations on the best way to do this and show the differences in a form?

[Micron]

I need to see if there are any cars in January that are not there in February. I also need to see if there are any cars in February that are not in January.

Did you try unmatched query wizard for either of these?

For example, if there is one less car on the right it is not easy to find if a user has hundreds of cars.

A report is usually better for reviewing data because you can group (e.g. by user) and use aggregate functions in controls, or create running sums, or sum over group, etc. etc. - much more powerful than what forms can do.

For the last issue, I'd try showing all of the cars on a rider but flag the one not being used in the report somehow. This could be a status field if you want to show more than one status. If you're only going to show one status (e.g. "Obsolete" then I'd make that a date field as it would flag it but also say when that happened).

[CJ_London]

you can use a left join to join the table to itself. Won't suggest what it looks like beyond the below because not enough information to provide an informed solution

This will display all the records in A where there isn't a matching record in B. somefield might be a date, a car id, a customer id, something else - for you to decide. And you may need to join on more than one field

SELECT A.*
FROM myTable A LEFT JOIN myTable B ON A.somefield=B.somefield
WHERE B.somefield is null

[ArviLaanemets]

Something like (on fly)

SELECT yt2.CarID, yt21.PaymentDate AS PaymentDate1, yt21.PaymentSum AS PaymentSum1, yt2.PaymentDate AS PaymentDate2, yt2.PaymentSum AS PaymentSum2
FROM YourTable yt2 LEFT OUTER YOIN YourTable yt21 ON yt21.CarID = yt2.CarID
WHERE Year(yt2.PaymentDate)=2021 AND Month(yt2.PaymentDate)= 2 AND Year(yt21.PaymentDate)=2021 AND Month(yt21.PaymentDate)= 1
UNION
SELECT yt1.CarID, yt1.PaymentDate AS PaymentDate1, yt1.PaymentSum AS PaymentSum1, Null AS PaymentDate2, Null AS PaymentSum2
FROM YourTable yt1
WHERE Year(yt1.PaymentDate)=2021 AND Month(yt1.PaymentDate)= 1 AND CarID NOT IN (SELECT CarID FROM YourTable WHERE  Year(PaymentDate)=2021 AND Month(PaymentDate)= 2)