Oh hey we just dealt with this earlier this year! So my family lives on the other side of the country and visits once a year, so having a queen bed taking up a lot of space in what is otherwise a playroom didn't make a lot of sense to us. Initially I was thinking air mattress, but who knows how long those are going to last, and even with the nicer ones you're still sleeping on an air mattress..
So we ended up getting a queen folding bedframe, and then this queen folding mattress:
https://www.amazon.com/Milliard-Folding-Mattress-Washable-Single/dp/B00W67PJ4MWe got the four inch mattress, which is fine (and you can always get a couple inches by putting on a mattress topper), but they also offer a 6-inch mattress as well. Given what you said, perhaps even the six inch, with a topper?
It folds up, and there are various cloth cases that fit it you can purchase on amazon as well (might even be linked on the page of the product link above).
With the mattress, folding frame, bed sheets and comforter and pillows and such, we are able to fit it all into one closet when they are not here and set-up and tear-down are super quick and easy.
My family is mid 60's, and usually stay for about one week each visit.
If you are just looking for a year-round guest bed, then personally we use a Tuft and Needle mattress (basic model) and find it comfortable.