Custom Autolist

[Guest]

Is there any way to create a custom range that rotates automatically?

I have one table which holds the names and contact information of service
providers and Yes/No field for each county that they serve.

I have another call for service table which may need to reference the
service providers and pick the next one in rotation for the county.

How can I reference to the service provider table, identify those providers
in the selected county, and pick the next one in the list?


Thanks,

 

[Guest]

Totaly different subject:
Have you considered placing a new table to hold Counties and ProviderIDs in
your database to help normalize your data a little bit more?
____________________________________________________
Attempt to answer question:
If you add a column to your table for date used you could then update that
field each time that provider is used. Your select statement would always
grab the service provider from your service provider table that had the
Yes/No field checked as yes for the desired county and then the oldest date
in the provider used field. Upon using that provider your program should
update that field with the current date and time effectively placing them
back to the bottom of the select list when you run it again. This will
effectively rotate the range of service providers for your desired county.

Example: your service provider table may look something like ...

ProviderID,
ProviderName,
ProviderInfo,
ProviderUsed,
ProviderCounty1,
ProviderCounty2,
ProviderCounty3

Then your select query would look something like :

SELECT tblServiceProvider.*
FROM tblServiceProvider
WHERE (((tblServiceProvider.ProviderID)=(SELECT
Min(tblServiceProvider.ProviderID) AS MinOfProviderID
FROM tblServiceProvider
WHERE (((tblServiceProvider.ProviderCounty2)=True) AND
((tblServiceProvider.ProviderUsed)=(SELECT
Min(tblServiceProvider.ProviderUsed) AS TargetDate
FROM tblServiceProvider
WHERE (((tblServiceProvider.ProviderCounty2)=True)))))));


-- Take Care & God Bless ~ SPARKER ~