Where to start?

[YTurnbull]

I am a Newbie - basics only

I work for a glass collection company - and I have numerous spreadsheets which
contain all our data. I want to bring all this data together and make use of it.

I have started a table with our site addresses and the days these sites need to
have a collection (sometimes more than once a week - decision being made manually as site added to database) and the number of bins that need to be collected.

I want to give each driver (two - currently) a list (report) of what sites they need to visit on a day .

The drivers report back to me with the number of bins collected and a weight collected from each site.

Ultimately I want to collate this information so I know how much has been collected from each site / averages etc.

Dont know how to go about creating a form that I can use when entering in the collection data - I want to be able to 'see' only the Sites which have a collection for a day (ie a Tuesday - not a specific date)

Really confused as to what to start first. Any assistance appreciated.:banghead:
Yvonne

 

[Pat Hartman]

Your site table needs to look something like:
tblSites:
SiteID (autonumber PK)
Address
City
State
Zip
OwnerID (foreign key to tblOwners)
NumOfBins
PickUpDay
...

PickUpDay would have a value of 1-7 with 1 being Sunday and 7 being Saturday. If you only do pickups on weekdays, you could restrict the range to 2-6.

On your form, use an unbound combo in the header to select the weekday. The combo should be defined as two columns and have a value list of:
1;Sunday;2;Monday;3;Tuesday;4;Wednesday;5;Thursday;6;Friday;7;Saturday
And hide the first column by setting its width to 0. That will allow the user to pick from a list of named weekdays.

The form's query should refer to this combo box.
Select ...
From tblSites
Where PickUpDay = Forms!frmSites;cboPickUpDay;

 

[YTurnbull]

Hi Pat,

How do I allow for two or more collection days for one site? ie. Site A- collection on a Monday & Friday.

I also have sites that are only collected once per month or fornightly or only when they call.

I had initially set the table thus (see attached) - with your previous suggestion I would need to alter this. Again where do I start? I keep going round in circles...:banghead:

Cheers

 

[Pat Hartman]

Although it may seem like overkill to you, once you have more than one of something, you have many and many requires a new table. Remove PickUpDay from tblSites and create a new table.
tblPickupDays:
PickUpID (autonumber PK)
SiteID (unique index field 1)
PickUpDay (unique index field 2)

Use a subform to display this new table.

PS - you may actually need to move some of the other data fields to this table. Examine each of them and ask yourself - could the value be different for each pickupday? for example could a different subcontractor pick up on Monday than the one that picks up on Thursday?