Preload list to aid data entry

[catbert]

This maybe be really simple, but I cannot seem to work it out.

I have a database which holds lists of recycling sites, and the days they are collected.

I want to help the users entering collection info for each day, by allowing them to select a day, and then the form will only display those sites collected on that day, in a particular sequence. Then the users can go down the list easily and enter amount collected from each.

In Access 97 I have several tables:
SiteDetails - contains address and site name details
Containers - for each site a list of the types of containers and sizes ( eg paper, glass etc)
Collectionday - for each site and each container the day of collection - and sequence number

For data entry the data is stored in two tables:
Collection - date, material collected, and total tonnage
Fill - for each site for each collection - the amount collected

I can easily display with a subform the collection dates and list of sites done on each day, and I have a query to display the "running order" for any day and type.

How can I get this query to display in a form, but then store the siteID and collection ID into the fill table with the collected amount.

I was thinking of after selecting day and type simply copying the sites into the fill table then displaying the fill table in a subform - but then I need the new collection ID. I feel a huge amount of coding coming on - can anyone give me a hint as to basic structure to keep this simple, maybe the best way to structure this? My coding skills are quite basic so this is a good chance to practice, but I'm not sure where to start - many thanks!

 

[SeeThru]

Is collection day a date field or is it a day of the week?

If it's a day of the week, a relatively simple query could be used to display the relevant sites:

"SELECT Site from sitedetails where collectionday=" & weekday(myDate) & " AND Containers='" & collectiontype

 

[catbert]

SeeThru, thanks for the reply.

the week is just a number, but I can retrieve this information, I have a parameter query to specify day and type. What I can't work out is how to add new tonnages to the list retrieved and then store the site ID, the new collection date ID and this tonnage into another table ( the fill table)

I can easily create a form that retrieves existing records - using the collection table, linked to a subform displaying the sites with fill info -

what I want to do is allow users to add a new collection date and list of sites without having to select each site from a drop down or type in each site in the subform - as the sites to be listed are known dependant on day and site I want to load the right sites dependant on the user selecting the day and type.

hope that is a bit clearer?

 

[SeeThru]

I'm trying to work out your data structure:

You have three tables: sites, Collection, containers with a one-many arangement and using the containers for lookup only.

(each site can have many collections. Each collection relates to one site and one type of container)

You are tring to add multiple records to the collections table. for each record you need to give it the date, the site and the container type


Sound like you may be able to use an append query with some parameters.

SQL could be:

INSERT INTO Collection ( CollectionDate, CollectionSite, ContainerType)
SELECT [forms]![myform]! AS Expr1, Collection.CollectionSite, [forms]![myform]![containerType] AS Expr2 FROM Collection;

This would add a collection record to ALL sites with the date as entered on your form (unbound control) and the container type as selected on your form.

You could further limit it by having a multi select list box of sites and passing that to the query.

I would run it from an unbound form that has some selectors (sites, date, collection type) that the user enters, and a button that builds the SQL and runs it as an update query.

 

[catbert]

that's great thanks, that points me in the right direction. I wasn't sure of the basic structure, syntax for this sort of thing.

Your basic understanding of my badly explained database is correct, however;

"(each site can have many collections. Each collection relates to one site and one type of container)"

each collection relates to a number of sites dependant on day - so I have a long list of sites, but e.g. Monday a subset of these is visited and I want to display just those to enter data against and append this to the correct table, then repeat for different list of sites next day and so on.

I think what you have suggested has put me on track so i will give it a go. Many thanks
CB