Access SQL Query to get Earliest date from linked table 2

[barnettjacob]

In a similar vein to the question I posted yesterday, I need to look at achieving the same thing with a slightly different approach in that I actually want to update the Guest table with the first booking date from the Bookings table:

Bookings
- Booking ID
- Created Date
- Guest ID

Guests
- Guest ID (Unique)

Effectively what I need is the SQL that pulls the Created Date from the booking table into a column in the guest table for that respective guest.

Thanks in advance
Jacob

 

[Andrzejek]

How about:
[tt]
UPDATE Guests
SET SomeDateField = ([blue]
Select Min(Created_Date)
FROM Bookings
WHERE Bookings.ID = SomeValue[/blue]
)
WHERE Guests.ID = SomeValue[/tt]

Have fun.

---- Andy

 

[SkipVought]

hi,

???

You really want that date in the Guests Table? REALLY?

I mean it's no problem to join the two on the GuestID and display the Created Date(s) in a query.

But logically, it does not belong there, as conceivably a guest could have many bookings over a period of time, at least that's what I would hope if I was running an establishment, attempting to get repeat business.

???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[barnettjacob]

Thanks Andy, appreciate you looking at it. Inserting my stuff where applicable I currently get a 'Syntax Error' as can be seen in the attached screenie. I've taken out the where clauses because initially I won't need them.

I basically understand what you are doing although I was surprised not to see a join of some sort. Do I need a relationship in Access to be activated for this to work?

Thanks again.
Jacob

 

[barnettjacob]

Skip, you've got me - basically I am trying to do this using mainly techniques I know and understand and I am going round the houses somewhat.

Fundamentally my goal is to be able to get a flag onto the Bookings table called 'Repeat' which is either true or false. Ideally there would also be a column detailing the number of previous bookings that customer had made against each booking record.

If I have the first booking date in the Guest table then its a pretty straightforward job to write a SELECT query in PowerPivot where I join the Bookings Table to the Guest Table and apply the flag on import to PowerPivot - if you can help me get there in one step in Access then that would be awesome!

Thanks
Jacob

 

[Andrzejek]

I agree with Skip, you don’t need to Update your Guests table with first Booking for that Guest. You already have this information in Bookings table. No need to keep the same data in multiple places (bad idea, IMHO)

So if you have a Guest with ID of 123, you can easily get the first date for this person:
[tt]SELECT MIN(Created_Date)
FROM Bookings
WHERE Guest_ID = 123
[/tt]
And if you want to see how many times this Guest visited, just do:
[tt]SELECT COUNT(*)
FROM Bookings
WHERE Guest_ID = 123
[/tt]

"attached screenie" does not show for me :-(

Have fun.

---- Andy

 

[barnettjacob]

Andy, not sure why the pic doesn't work although its pretty irrelevant at this point. The end Bookings table I'm looking for has the following columns:

Booking_ID
Guest_ID
Created_Date
Repeat
Customer_Booking_Number

Where the first three are from the data export and the last two are calculated based on the 'current' customer's history.

Hope this makes sense.

Jacob

 

[Andrzejek]

I would question your ‘Repeat’ field “which is either true or false”. It does not really do anything. The ‘SELECT COUNT()’ will give you the number of bookings for the Guest.

And I would guess the ‘Customer_Booking_Number’ field is just another way of presenting ‘Booking_ID’ I may be wrong here, but if I am right, I would create my Booking_ID based on Date and Guest_ID field and forget about “Customer_Booking_Number”. If my Guest (123) would book a stay starting a week from now, I would have Booking_ID something like 123140218 (123 Guest #, 14-02-18 date of stay). And maybe even throw how many days the guess will stay.

Or my ‘Customer_Booking_Number’ field would be calculated (concatenated) field out of Booking_ID, StartDate, Days_to_Stay. Something you can get ‘on-the’fly’ without any field in the table…

Just a guess here, I am sure there are many other ways to do it.


Have fun.

---- Andy

 

[barnettjacob]

Thanks Andy!

 

[PHV]

Anyway, this is rarely a good idea to store derived/calculated values.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886