Ranges - zones of occurance (1 Viewer)

[yippie_ky_yay]

hello forum,

While looking over some old dbs we have here, I noticed that one of them lists the zones of occurance by manually typing in "Zone1" or "Zone1-4".

This seemed like something easy to fix up at first, but then I noticed that there are also entries like "Zone1-3,5-7" (meaning 1-7 with the exception of 4).

Has anyone seen a better way to standardize this?

Thanks!

-Sean

 

[Dave Eyley]

Depending on the number of zones required, you could create a checkbox for each zone. That way, the user can select any permutation of zones.

It will need some code record the selection for the tables.

Dave E

 

[Rich]

Or a multi select list box

 

[yippie_ky_yay]

Thanks for answering guys!

Those are great ideas if I ever set up user forms, but I don't really need a user-friendly answer for this - whatever will help me best for querying and reporting! I think creating columns for all 20 zones may be a bit much (there would be many blanks/nulls!)

Thanks again,

-Sean

 

[Pat Hartman]

When you have a many-to-many relationship, it takes three tables to properly implement. In your case, you would need a new table that defines the 20 zones. More could of course be added with no programming required since you would be adding rows to a table rather than columns. The second table is the existing one and the change would be to remove zone entirely. The third table would be the relationship table. This table "links" rows of the Zone table with your original table. For the sake of an example, we'll call this tblCompany.

tblZone
ZoneID
ZoneName

tblCompany
CompanyID
CompanyName
etc.

tblCompanyZone
ZoneID
CompanyID

With this structure, a company can be linked to as many zones as necessary. This structure can do any necessary zone analysis with simple queries rather than complex VBA code.

The current solution does not lend itself to any reasonable type of analysis. Anything you do would involve parsing the mushed data in the zone field to normalize it before any reporting could be done. The suggested solution of using a multi-select list box also requires a lot of VBA coding since multi-select list boxes can NOT be bound. The mushed data that results from this technique would also need to be parsed and normalized before any meaningful reporting could be done.

 

[yippie_ky_yay]

Thanks Pat!

I am going to try to implement this and see how successful my reports turn out. At worst it will be a good learning experience!

Cheers!

-Sean