I am a newbie trying to create a form in Access based upon the following table/fields:
TABLE=Data
FIELD1=Carrier_Name
FIELD2=Total_Receivable
FIELD3=Total_Weight
FIELD4=Close_Out_Date
I've created a combo box and a DISTINCT command that allows me to choose a Week Ending date for the last two years. When I choose a week ending date in the combo box, I want the form to populate each CarrierName (once), the carrier's TotalCost, and each carrier's TotalWeight for that week ending date. I was able to do this in the reports but can't figure it out in the forms.
At the bottom of the reports I also had totals and frequencies. To count the number of times each carrier had a shipment that week ending date I used Count(DATA.CLOSE_OUT_DATE)
AS FREQUENCY, to sum the totals I used Sum(DATA.TOTAL_RECEIVABLE)
AS [TOTAL COST], to sum the weight I used Sum(DATA.TOTAL_WEIGHT) AS [TOTAL WEIGHT], and to get an average of weight over cost I used [TOTAL COST]/[TOTAL WEIGHT]*100 AS [C/W].
If someone can guide me on how to populate a text box for each Carrier_Name (once), the carrier's Total_Receivable, and Total_Weight based upon the Close_Out_Date in the combo box I'd apprecaite it. Also, some guidance on the totals based upon the SQL above would also be apprecaited! I'm somewhat familiar with SQL, but I've never used expressions before and I think that's what I'm supposed to use to populate these fields.
Here's an example of the desired form:
Choose Week Ending Date:
6/19/2007 <--COMBO BOX DOWN DOWN MENU
CARRIER NAME FREQUENCY TOTAL COST TOTAL WEIGHT C/WT
Abc Trucking 2 $1,600 75,000 $2.13
Xyz Express 51 $69,790 584,208 $11.95
GRAND TOTAL 53 $71,390 659,208 $10.82
Start Free Trial
