Region and Zipcode

[Dented]

Hi,

I am new to Access and new to forums in general. Please excuse me if I do not frame this question well.

I have a table that groups Zip Codes into Regions. Another table uses these Zip Codes to display (among other things) Plants that bloom in this Zip Code at a certain time of year.
When I enter the parameter Region, the result I get is one record per Zip Code for each Plant. So for example, 20 lines of Climbing Aster because there are 20 Zip Codes in the Region.

What is a simple way to only return one Plant per Region?



Thanks,

[pbaldy]

It would help to see the SQL of your query. It sounds like you have zip code as a field with Group By, and you don't want it.

[Dented]

Hi pbaldy,

Thanks for replying and sorry I took so long to respond. I hope this is what you were asking for.

SELECT qselCriteria_GardenSize.GardenSizeID, qselCriteria_GardenSize.GardenSizeName, qselCriteria_HardinessZones.HardinessZoneID, qselCriteria_HardinessZones.dbo_HardinessZone.Name , qselCriteria_Light.LightTypeID, qselCriteria_Light.Name, qselCriteria_SoilMoisture.SoilMoistureID, qselCriteria_SoilMoisture.GardenSoilMoistureName, dbo_Plant.CommonName, dbo_Plant.ScientificName
FROM qselCriteria_GardenSize, dbo_BF_Plant INNER JOIN (((qselCriteria_HardinessZones INNER JOIN dbo_Plant ON qselCriteria_HardinessZones.PlantID = dbo_Plant.PlantID) INNER JOIN qselCriteria_Light ON dbo_Plant.PlantID = qselCriteria_Light.PlantID) INNER JOIN qselCriteria_SoilMoisture ON dbo_Plant.PlantID = qselCriteria_SoilMoisture.PlantID) ON dbo_BF_Plant.PlantID = dbo_Plant.PlantID;

[orange]

@Dented

Here is a link to info in several formats related to Database Planning and Design.

I strongly suggest you work through the tutorials from RogersAccessLibrary that are identified in the linked info. Create a description of what you are trying to accomplish in plain simple English, then build a data model as per the tutorials. Test it with sample scenarios and when you have the logic and structure working, that's when to design the database with Access. You will have a blueprint to follow.

There are other tutorials and videos to assist with concepts and analysis.

Good luck with your project.

[Dented]

I'm going to take a look at that right now. Thanks!