Access or Excel?
Sorry for the long description of what I am trying to achieve but if you guys know all the facts then I guess it is easier to advise.
I am looking to build, either in Excel or Access, an ever-increasing list of textual data (a database?) then access this monthly to pull out clients' details and invoice them for any work we have done for them. Also to be able to cross-reference the text data for analysis. Sounds pretty simple but the architecture behind the database is what I need to get right before filling in the gaps.
For example: imagine a courier company with a client list of say 50 customers. Each customer emails their order daily. This textual data (delivery/ collection address and size/ weight of parcel etc.) is then manually added to the ‘ever-increasing list’. So, I imagine the excel heading line would look like this:
Client code: date: Instruction: ‘then 5 columns of address info’
(divided up into house number /name, street, town, postcode which all could/will be used later to cross-reference/analyse data)
I envisage this being entered by an operator using excel drop-down lists and data validation and would like some autofill help or behind the scenes second-guessing to fill in some remaining fields to speed up the process. ( Ie if it recognises an address previously in the system than add data in the final column of ‘how to find us’) – so some find/display/ textual info. formula would be great.
I understand that ‘a button’ could be used to run a macro? Would this suit this application?
At the end of the billing month, I would like to produce an invoice for up to each of the 50 clients individually. The formula for the pricing structure (stored in a separate worksheet along with client ID codes) would add subtotals and total as well as display all the textual data sorted and separated by a given subheading parameter (ie. All items under 1KG, 1- 2KG, etc, etc)** this is an important function of the process so that the instruction (ie collect) will have a lists of collection addresses itemised with a subtotal for costs; followed by a list of addresses for 'delivery' etc.
I’d like this to happen automatically when the month is selected (filtered?/sorted?) then the client code selected. So, would I have to have a different worksheet for each client? Or could my goal of one single list be achievable?
Note:
1] As almost all of the data is textual, with exact matching filtering/sorting is it better to use Access or Excel?
2] Would I need to filter firstly by date (month) then by the client before producing an invoice?
3] Would I be able to get an invoice to autofill, for instance, under a subheading ‘Collection / DeliveryAll packages under 1KG’ and then list all the delivery addresses and dates. Then next subheading All 1-2KG etc.
Many thanks for taking the time to read such a long scene setter
Gareth
