L
lfaherty
Hi MS-Access friends :.) I need some help on how to
display a matrix which acts sort of like a pivot table
only editable and without aggregate functions.
My intention is to produce filterable columns/rows/cells
which would display already entered values in the
intersecting cells and display empty cells which would
create corresponding new records dependant upon selected
filters...
-ExpenseIDs | Hours | Dollars
-ENG
-CON
-PUB
-OTHR
-ODC
My applicable tables and fields include...
-tblJobs (jName)
-tblExpenses (eName, eChargeID, eExpenseID, eDollars,
eHours)
-tblSortExpenses (sExpenseID, sSortNum)
My Main Form's record source is tblJobs and includes
controls intended to filter the subform...
-txtName (bound to the jName field and used as the
subform's master field)
-cboChargeID (drop list= -1000, -2000, -3000, -4000).
My subform's record source is a query that sorts the
expenses as desired (the Name field is used as the
subform's child field)...
SELECT tblExpenses.eName AS Name, tblExpenses.eExpenseID
AS Expense, tblExpenses.eHours AS Hours,
tblExpenses.eDollars AS Dollars
FROM tblExpenses LEFT JOIN tblSortExpenses ON
tblExpenses.eExpenseID = tblSortExpenses.sExpenseID
ORDER BY tblSortExpenses.sSortNum;
How do I get the subform to filter dependant upon the
selected ChargeID in the main form? I couldn't define it
as a secondary master/child field because the combo box is
unbound.
How do I always display all ExpenseIDs? My query produces
the matrix but it doesn't display ExpenseID rows when
there's no record that corresponds to the selected filters.
How do I add a new record into tblExpenses when values are
typed into empty cells?
Is their's a better way to go about this?
TIA for any help; and I apologize for the lengthy
explanation.
display a matrix which acts sort of like a pivot table
only editable and without aggregate functions.
My intention is to produce filterable columns/rows/cells
which would display already entered values in the
intersecting cells and display empty cells which would
create corresponding new records dependant upon selected
filters...
-ExpenseIDs | Hours | Dollars
-ENG
-CON
-PUB
-OTHR
-ODC
My applicable tables and fields include...
-tblJobs (jName)
-tblExpenses (eName, eChargeID, eExpenseID, eDollars,
eHours)
-tblSortExpenses (sExpenseID, sSortNum)
My Main Form's record source is tblJobs and includes
controls intended to filter the subform...
-txtName (bound to the jName field and used as the
subform's master field)
-cboChargeID (drop list= -1000, -2000, -3000, -4000).
My subform's record source is a query that sorts the
expenses as desired (the Name field is used as the
subform's child field)...
SELECT tblExpenses.eName AS Name, tblExpenses.eExpenseID
AS Expense, tblExpenses.eHours AS Hours,
tblExpenses.eDollars AS Dollars
FROM tblExpenses LEFT JOIN tblSortExpenses ON
tblExpenses.eExpenseID = tblSortExpenses.sExpenseID
ORDER BY tblSortExpenses.sSortNum;
How do I get the subform to filter dependant upon the
selected ChargeID in the main form? I couldn't define it
as a secondary master/child field because the combo box is
unbound.
How do I always display all ExpenseIDs? My query produces
the matrix but it doesn't display ExpenseID rows when
there's no record that corresponds to the selected filters.
How do I add a new record into tblExpenses when values are
typed into empty cells?
Is their's a better way to go about this?
TIA for any help; and I apologize for the lengthy
explanation.