What is a good car that can hold 6-8 people and had good gas milage?

I don't want a minivan. Right now I have a ford freestyle. I'm looking for under $5,000 and can get around to 40 miles per gallon

Not gonna happen, Skippy. You're talking full sized 8 or 9-passenger SUV here, and at best, they will get 16-18 miles per gallon.

Fix yours and save the money from a lost hope again. 5,000 won't get you into any more recent vehicles which all are the most fuel efficient. Ford flex as an example fits your bill pretty well but is well above your number.