11-15-15, 14:21 #1Registered User
- Join Date
- Nov 2015
Unanswered: Pass Query Results to Form Based on Table
New here, and somewhat new to databases, but here's the problem.
It has to do with trucking routes. I have multiple drivers going to multiple locations and making pickups.
A specific driver has a set route each weekday (so, for example, Bob has a Monday route, a Tuesday route, etc and Jill has her own Mon-Fri routes). On each route are various stops - different stores belonging to different organizations.
I want to be able to pull all the information together so that I can (for example), ask how many total pickups were done for a given organization (looking at all stores on all routes over a given timeframe). To do this, I've got a "pickups" table that includes driver name, store information, date, weight of pickup, type of pickup, etc.
To make life easier for the user, I'd like to open a form that displays the stops of a given route and lets them enter pickup information (weights and types).
So far, I have a form where the user can enter two values - Driver Name (from a lookup) and date, and then press a button, which starts a query. The query then grabs the route for that driver and whatever day of the week the date falls on.
So far, so good.
Next, I want to have the query results - showing all the stops on the route - open in a form so that I can enter other information (Item Delivered, weight) and save that in a table of all transport information. All this so that the user doesn't have to enter the route stops.
This last part I can't figure out.
I think I might be able to do it by having a recordset for the form and the query, and pass info from the query recordset to the form recordset. Am I on the right track? Is there a better way to do this?
Thanks - hope this is clearer than the mess in my head!
11-15-15, 15:16 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
make your head more mess up
You could later Use Google Maps to display it
My under standing on what you are saying it
a drive select his/her route which them show in a subform all pickup then you could get the user to double click on the stop which would show a popup asking the (Item Delivered, weight) them when they close it I hide that stop as its been done.
post some screen shots and table layout then we could Point you down the right trackhope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON
11-15-15, 16:46 #3Registered User
- Join Date
- Nov 2015
some clarification (?)
I can't post many details, since the work place is strict about privacy issues. But here's a scaled down version of the problem:
I have stores A through H, and three drivers, Bob, Tom, and Jill. Bob’s route is stores A,B,C,D. Tom’s route is stores A,C,G,H, and Jill’s route is stores D,E,F,G.
Behind these are “Driver” Table, with driver name, ID, and truck info; and “Store” Table, with store name (A-H), address, phone number, and contact person.
We’re collecting all of the information about items picked up at each store into a “Pickup” table, with Fields: Date, Driver, Store, WeightOfFurniture, WeightOfBooks, WeightOfClothes
The user starts with the driver’s name and date, clicks a button, and this opens a form with the following fields:
Driver, Date, Store, WeightOfFurniture, WeightOfBooks, WeightOfClothes
with driver and date filled in based on the initial entry, and “Store” having all listings for a given driver’s route.
So I select Bob and a date (11-12) and get a form with:
Name Date Store WtFurn WtBooks WtClothes
Bob 11-12 A
Bob 11-12 B
Bob 11-12 C
Bob 11-12 D
and all the great data gets saved in the "Pickup" table so I can find out how much of a given item was picked up at a given store in a certain date range (or whatever . . .)