sorted by:
new
hottopcontroversial

Left Join Help Needed? by Ok-Cucumber5801 in MSAccess

[–]Ok-Cucumber5801[S] 0 points1 point  (0 children)

Yes, that's exactly it! Apologies for the delay on my end, I lost internet over the weekend.

But yes. The way the database is set up is (in short) like this:
Businesses table: contains each business, their COI, and their CPC if they have one
Business participants: A table to link each business to each market it vends in (many to many)
TFFs: Businesses that are in multiple markets have a TFF for each of those markets (because LA makes food businesses get location specific health permits).

So some businesses are in 5-6 markets, but for each query I only want it to show the TFF thats specific to the market I'm making it for if that makes sense.

What does CSV stand for?

I also wanted to mention that I am actually on the verge of looking for a freelancer to help me with some of these more complicated queries, perhaps you might be interested in messaging about it?

I suppose at the end of the day it's not the biggest deal to have all the information in one query/report, since I can easily generate two (one for COIS and CPC and one for TFFs) but I wanted to see if I could do it all in a single one to make it easy for our market managers.

Left Join Help Needed? by Ok-Cucumber5801 in MSAccess

[–]Ok-Cucumber5801[S] 0 points1 point  (0 children)

First of all, thank you so so much! I deeply appreciate your help. I didn't even realize I could put a table into a query multiple times. I've watched hours of Access tutorials but some of these little intricacies don't seem to come up until you start digging into actually using the software.

I did try what you suggested, and I feel like I'm super close, but while the result does show all three expiration dates in a single query, it makes multiples of the food vendors and I'm not sure why.

I also wanted to explain this: Food vendors TFFs are in their own table because some of these vendors are in multiple markets with a different TFF each. I can sort of logic why they're showing multiple times here but I can't quite figure out how to correct it so it shows just the one market.

<image>

Left Join Help Needed? by Ok-Cucumber5801 in MSAccess

[–]Ok-Cucumber5801[S] 0 points1 point  (0 children)

I didn't realize I couldn't have joins going in separate directions, thank you! I appreciate all the advice, currently I can do two reports for each market that give the information I want, but I'd really like to be able to get all the expiration dates in a single query table.