Creating an invoicing system (migrating from Excel to Access)

[reubendayal]

Hi There,



I am new to Access 2013. I've seen a lot of learning videos on MS Access. I am migrating from Excel to an Access database and am developing it for a small team.

Right now I have migrated all my data to the database. But I'm not sure how to create the invoicing system.

The issues I'm facing:

1) each client has different pricing strategies and rates. Therefore, their prices are in different excel sheet on a common network drive. - How do I import/link these prices in to my access database?

2) earlier I was able to calculate the applicable costs for my invoicing system using a long complex IF functions in excel. But I do not want to go that route as its scope is limited and specific. Will appreciate any suggestions to restructure how the prices are calculated in the database.

3) as our services are related to the kind of support we are providing to our clients, we offer different prices for each applicant and their respective family (if any). I'm not quite sure how I should organize this information in the new database.

Thank you in advance of your valuable suggestions and support.

Regards,
Reuben

[NTC]

Microsoft has studied to find that nearly 80% of Access database begin on excel - so you are in good company.

1. Import those into tables in Access; and abandon them as excel sheets. It is much easier to manage within Access. Based on my experience then you will want to consolidate them into a single table. In order to do so you will need to append the client's unique ID/Name to each of their records before co-mingling them all together.

2. the only generic advice one can give without knowing the specifics is that using a query one can create calculated fields; and one can chain those together as needed. I have always been able to make a pricing style calculation. Admittedly it can become fairly complex particularly if one must pull off of other rate tables.

3. this is not unusual - as per item 1 - you assign their identity to their records.

The reason you can't have separate tables for separate clients - is because then you would have to have separate controls (drop down boxes, text boxes, etc) in each form or potentially separate forms - and that is an incorrect design structure. Excel and a relational database are different and one has to think in database terms.

[reubendayal]

Thanks so much for your quick reply. I already feel welcomed to the group!

Regarding the database, I have imported all the data from Excel in to the Access database. But now, I have a few problems with getting my pricing data going. In the past, when I used it for my own use the pricing was simple as I had only one large client to cater and their prices were fixed so the calculation was easier. However, now as I plan to share this database to be used by two of my other team members it is necessary that I find a solution to the pricing issue.

I'm thinking of an option (macro or hyperlink option or any other) to link the price table each time a new client is being added to the database. And once setup it would simply use that data for the subsequent cases. As stated earlier, the rates are specific for each client and is saved in different rate sheets on the common network drive. So for now my first challenge is how I pull that data in to the database, and then to make the calculations.

Regarding the database, typically our service entails a fee for each applicant and this varies if we have dependents of the main applicant included in our service.
I would then pull that data in to an invoice template to complete this step.

Could you please suggest a way around this.

Thanks again.

Reuben

[NTC]

Based on my experience then you will want to consolidate them into a single table. In order to do so you will need to append the client's unique ID/Name to each of their records before co-mingling them all together.

an option (macro or hyperlink option or any other) to link the price table each time a new client is being added to the database - should not be considered.

[reubendayal]

Hi There,

Thank you for the suggestion. I have convinced my boss to use just one table for the pricing and update it each time there's price change.

Could you suggest a way to how I could calculate the prices for each application type.

In excel I had a huge IF function doing this for the main customer, and separately for his family. But then I was wondering if this had to be so complicated in Access.

Here's an example of the IF formula I was using:

=IF(ISBLANK(F149);" ";(IF(F149="AR1";'IBM PRICE LIST'!$B$8;(IF(F149="AR1 & FA8";('IBM PRICE LIST'!$B$8);(IF(F149="AR1 
(Conv. From Corp. To Pay Lim. Sch.)";'IBM PRICE LIST'!$B$8;(IF(F149="AR1 & FA9";('IBM PRICE LIST'!$B$8);(IF(F149="AR3";'IBM PRICE LIST'!$B$23;(IF(F149="FA8";'IBM PRICE LIST'!$B$8;(IF(F149="AR3 & FA9";'IBM PRICE LIST'!$B$23;(IF(F149="AR3 & FA8";'IBM PRICE LIST'!$B$23;(IF(F149="FA9";'IBM PRICE LIST'!$B$23;(IF(F149="EU Regst.";'IBM PRICE LIST'!$B$27)))))))))))))))))))))

So you see this would take me a long time to replicate in Access.

Any alternate suggestions?

thanks.
Reuben

[NTC]

Just to be clear; while all clients are in 1 table - you can do a query of just 1 client - and that result displays to you as if the client had their own table in terms of updating/managing. So there should be no downside to a single table approach.

Plus now if Item A changes price - and is common to all clients - you can run a single Update Query and change it for all clients in one easy step.

I have no idea what is the intent of the excel IF statement you display - and so cannot comment.

[reubendayal]

Thanks again.

I understand your suggestion on how to layout data in the pricing table. Thank you for the same.

My question now is how this can be calculated in the database. The 'IF function' from Excel was the one I was using to make these calculations and has not much to do with the database, it was only to give you an example.

Due to my basic skills on Access, I'm having difficulties picturing how I can establish the invoicing system.

Thanks.
Reuben

[ssanfu]

People that switch from Excel to Access generally have a harder time than someone without Excel experience. You almost have to forget anything you know about Excel (except VBA programming). This is because Excel is "flat" and Access is "relational". Worksheets generally do not translate into tables very efficiently.

While you have not provided much info about your process, I can see at least 3, maybe more tables.
One table for client info, one table for Pricing and one for Services provided.... depends on your data.
We started with 3 basic worksheets in a workbook and now have 28 tables.

So at this point, pencil and paper (or cardboard or white board or sticky notes or ...) is your friend.
Draw your tables and fields. Set relationships. Pretend to add data.... see how it works. Refine your tables/fields.

As to your calculations, how would you calculate a price manually? Write down the steps. The Excel formula you provided means nothing - we don't know your spreadsheet layout.
It sounds like it will probably require code to do the pricing calculation.

ALL of my tables have primary keys that are Autonumber type. Do not name the PK fields "ID". It gets confusing..
For the client table, use something like "ClientID". I use "ClientID_pk". In a related table, the foreign key field is named "ClientID_fk".

Also, do not use spaces, puncutation or special characters (except the underscore) in object names.
See: http://access.mvps.org/access/tencommandments.htm and http://access.mvps.org/access/lookupfields.htm

You can post a pic of your relationship window or post your dB if/when you have questions.
If you post your dB, be sure to munge any sensitive/confidential data - just need enough records to see how the data is related/interacts

I'm having difficulties picturing how I can establish the invoicing system

There are a lot of free database templates that you can view at http://www.databaseanswers.org/data_models/index.htm
see http://www.databaseanswers.org/data_...ices/index.htm



My $0.02 worth.

[reubendayal]

Thank you for your detailed reply, Steve.

I have migrated all my data from my excel workbook, which had most of my data in about 3 worksheets and a few more for background sheets for calculations. But it was just too much I was trying to achieve through Excel.

So I've migrated to Access. And you're right, even after going through a lot of online courses on Access, I'm still a bit unsure finding my way around.

I have taken time to restructure data to the best I can and have spent considerable amount of time doing so. You could be right about the requirement of code for doing the calculations. But I will cross that bridge when I get there. For now I will refine the database further and come back with an example of the db for you to look and comment on.

Thanks for sharing the above links on the data models, but I could only get to seeing the data models the templates need to be requested for via email. Any other suggestions of where I could get more options for free templates.

Thanks again.

Reuben

[ssanfu]

but I could only get to seeing the data models the templates need to be requested for via email. Any other suggestions of where I could get more options for free templates

You print the example, then create the dB with your requirements - your field types, additional fields, deleting the fields not needed. Lots of typing...