Hi all,
I've posted this here as I think the Form design will be the trickiest part of what I am trying to achieve.
I have tried amending some examples that are close to what I want but I haven't been able to quite get there as my VBA is not advanced. The closest I have found to what I want is this example by Banana http://access-programmers.co.uk/forums/showthread.php?t=162765
The main difference is that I have tblItems instead of tblAttendee, tblCustomers instead of tblClass, will only have 12 periods (each month of a year) and want a data type of Double instead of a CheckBox.
I have attached my starting point database with the simple tables with no relationships. tblPlanner is the 'final' table and should have a record for each customer/item/month permutation.
I will then want a form/subform where the form ComboBox will be linked to the tblCustomer and the subform has Items by Month (ie Crosstab).
What I am trying to achieve is a form where the end user can choose their customer, then see a record for every item with Jan through Dec quantities, and be able to enter and amend those quantities throughout the year i.e. plan what quantities they think they will sell of each item by month (by customer).
I have attached a spreadsheet showing what I mean about the form as that will explain it best!
That is the only data entry a user will do. They will not add Items, Months or Customers. The tblCustomer and tblItems can expand and contract as they are based on an external SQL database.
This has been frustrating me for over half a year now as I want to move away from stacking 90 or so Excel sheets into Access!
Any advice/links will be greatly received.
Nick
I've posted this here as I think the Form design will be the trickiest part of what I am trying to achieve.
I have tried amending some examples that are close to what I want but I haven't been able to quite get there as my VBA is not advanced. The closest I have found to what I want is this example by Banana http://access-programmers.co.uk/forums/showthread.php?t=162765
The main difference is that I have tblItems instead of tblAttendee, tblCustomers instead of tblClass, will only have 12 periods (each month of a year) and want a data type of Double instead of a CheckBox.
I have attached my starting point database with the simple tables with no relationships. tblPlanner is the 'final' table and should have a record for each customer/item/month permutation.
I will then want a form/subform where the form ComboBox will be linked to the tblCustomer and the subform has Items by Month (ie Crosstab).
What I am trying to achieve is a form where the end user can choose their customer, then see a record for every item with Jan through Dec quantities, and be able to enter and amend those quantities throughout the year i.e. plan what quantities they think they will sell of each item by month (by customer).
I have attached a spreadsheet showing what I mean about the form as that will explain it best!
That is the only data entry a user will do. They will not add Items, Months or Customers. The tblCustomer and tblItems can expand and contract as they are based on an external SQL database.
This has been frustrating me for over half a year now as I want to move away from stacking 90 or so Excel sheets into Access!
Any advice/links will be greatly received.
Nick
