PowerPivot Access SQL Query to add first date from related table. 1

[barnettjacob]

Hey, I'm trying to query an Access Database with PowerPivot. Long story short I am trying to bring in the date of a Guest's first booking from the Bookings table with their ID, Country and the Date their account was created from the guest table.

I've done this a million times from SQL Server databases but when I try with Access I get an error about a Type Mismatch. Here is the query as it stands:

Select

[Guests].[GuestID],
[Guests].[StartDate],
[Guests].[Country],
Min([Bookings].[BookingDate])

from [Guests]
join [Bookings] on [Bookings].[GuestID]=[Guests].[GuestID]

Group by 
[Guests].[GuestID],
[Guests].[Flat Date],
[Guests].[Country]

Thanks
Jacob

 

[PHV]

You select StartDate but group by [Flat Date] ?

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

 

[Andrzejek]

Your 'Select' part does not match your 'Group By' part.

Try this:

Select

[Guests].[GuestID],
[Guests].[blue][StartDate][/blue],
[Guests].[Country],
Min([Bookings].[BookingDate]) [red] As Something[/red]

from [Guests]
join [Bookings] on [Bookings].[GuestID]=[Guests].[GuestID]

Group by 
[Guests].[GuestID],
[Guests].[blue][StartDate][/blue],
[Guests].[Country]

Have fun.

---- Andy

 

[barnettjacob]

Guys, sorry but this was just a typo when I was tidying up the code to put in the post. My Select and Group By do match!

 

[PHV]

What are the data type of Bookings.GuestID and Guests.GuestID ?
Furthermore, no WHERE nor HAVING clause in your real query ?


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

 

[barnettjacob]

PHV - looks like that is the problem. One is text and the other is a number (one came from an Excel export, the other from CSV as it was so large).
Jacob

 

[PHV]

So, use compatible data type on your join, with either the Val function (to compare numeric columns) or the concatenation operator ( & "")

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

 

[barnettjacob]

PHV - sorted. You were a massive help!