Quickest method for adding order preferences (1 Viewer)

[Abigail Florence]

Hi there.
I have an client who wants a method of adding their clients orders to a list in alphabetical order which they will then print out for their records. Currently, they use Excel and sort it manually. This method obviously has numerous human errors which they would like to reduce. The order includes links to a price list which changes every month. This is a time consuming task and they would like to make their company run in a more structured and time efficient method. They want to know if it will be more beneficial for them to switch to Access. I have tried thinking of a way to do this, but haven't managed. I'm sure there is a way but I don't know how... Would you have any suggestions for me?

Many thanks.

 

[theDBguy]

Hi. Welcome to AWF!

Are you able to post a sample file or screenshots to further explain the requirement?

 

[gemma-the-husky]

How do you intend to sort clients names. Are you showing surname first, then forename for non company clients? You don't need to add them to the list anyway, you just need to be able to sort the list.

 

[GPGeorge]

Hi there.
I have an client who wants a method of adding their clients orders to a list in alphabetical order which they will then print out for their records. Currently, they use Excel and sort it manually. This method obviously has numerous human errors which they would like to reduce. The order includes links to a price list which changes every month. This is a time consuming task and they would like to make their company run in a more structured and time efficient method. They want to know if it will be more beneficial for them to switch to Access. I have tried thinking of a way to do this, but haven't managed. I'm sure there is a way but I don't know how... Would you have any suggestions for me?

Many thanks.

The immediate answer is that Access is quite likely to be a much more efficient tool. In the short run, however, you will need to invest time and resources into, first, learning how relational database applications such as Access need to be designed, and second, translating that understanding into the appropriate design of the tables. You will need multiple tables to properly create a relational database application. The process of designing and building them is called Normalization. That's the starting point.

One important thing to keep front and center in your planning is that Excel and Access are two different applications, subject two different sets of conditions and features. Don't think of "Excel-like" behaviors as a guide to doing what needs to be done in Access. Thinking in Excel terms will lead to flawed designs in Access.

Tables in Access are unordered sets of records. That is to say, they have no inherent sort order at any time. When you select some or all of those records with a query, then you can impose a sort order on that set of records in that query. But once you decide what that sort order needs to be, and create and save a query to return the records in that order, it's replicable over and over and over no matter how many new records are added.

 

[Abigail Florence]

Hi. Welcome to AWF!

Are you able to post a sample file or screenshots to further explain the requirement?

Thanks for your support. This is a screenshot of the price list. Each person gives in their order to the company by paper.

This is a screenshot of a sample of the orders. The secretary uses the VLOOKUP formula to link their order with the price. The name of each client goes at the top each order. This is what they want in alphabetical order so that when it is printed, they can easily find the clients name. Currently they order it by hand before inserting it into the spreadsheet. Another point to keep in mind is some of the items have VAT separate to the price shown(that is what 'taken' refers to). The client wants that each order will state whether VAT has been added to the items or whether no VAT was required. This sheet is printed and the cheque/bank transfer check box is ticked off once the order has been paid.
I hope this has been explained clearly enough so that you can explain, do you feel this is something that can be done through Access?

How do you intend to sort clients names. Are you showing surname first, then forename for non company clients? You don't need to add them to the list anyway, you just need to be able to sort the list.

I want to sort the list by surname first. Once all the order forms have been inserted in the system, I want to be able to sort this alphabetically instead of doing it manually which is what the client is currently doing. Would you feel this is possible?

The immediate answer is that Access is quite likely to be a much more efficient tool. In the short run, however, you will need to invest time and resources into, first, learning how relational database applications such as Access need to be designed, and second, translating that understanding into the appropriate design of the tables. You will need multiple tables to properly create a relational database application. The process of designing and building them is called Normalization. That's the starting point.

One important thing to keep front and center in your planning is that Excel and Access are two different applications, subject two different sets of conditions and features. Don't think of "Excel-like" behaviors as a guide to doing what needs to be done in Access. Thinking in Excel terms will lead to flawed designs in Access.

Tables in Access are unordered sets of records. That is to say, they have no inherent sort order at any time. When you select some or all of those records with a query, then you can impose a sort order on that set of records in that query. But once you decide what that sort order needs to be, and create and save a query to return the records in that order, it's replicable over and over and over no matter how many new records are added.

Thanks for your detailed answer. I am aware of the basics of Access but I don't know any code so I don't know if this is the right turn to make. I could continue to work off Excel but I thought Access may be more time efficient and have other advantages. I would be delighted if I could have support such as through this forum but if you feel this would be too time consuming and complicated to learn perhaps this isn't the correct approach?

Much appreciated for your support.
Abigail

 

[GPGeorge]

View attachment 104455
Thanks for your support. This is a screenshot of the price list. Each person gives in their order to the company by paper. View attachment 104456
This is a screenshot of a sample of the orders. The secretary uses the VLOOKUP formula to link their order with the price. The name of each client goes at the top each order. This is what they want in alphabetical order so that when it is printed, they can easily find the clients name. Currently they order it by hand before inserting it into the spreadsheet. Another point to keep in mind is some of the items have VAT separate to the price shown(that is what 'taken' refers to). The client wants that each order will state whether VAT has been added to the items or whether no VAT was required. This sheet is printed and the cheque/bank transfer check box is ticked off once the order has been paid.
I hope this has been explained clearly enough so that you can explain, do you feel this is something that can be done through Access?


I want to sort the list by surname first. Once all the order forms have been inserted in the system, I want to be able to sort this alphabetically instead of doing it manually which is what the client is currently doing. Would you feel this is possible?


Thanks for your detailed answer. I am aware of the basics of Access but I don't know any code so I don't know if this is the right turn to make. I could continue to work off Excel but I thought Access may be more time efficient and have other advantages. I would be delighted if I could have support such as through this forum but if you feel this would be too time consuming and complicated to learn perhaps this isn't the correct approach?

Much appreciated for your support.
Abigail

First, it is well worth the investment in time to have a solid solution. So, no I do not think it is too time consuming and complicated.

Second, screen shots are a distant second choice when supplying samples of data. We would have to laboriously read and type the values from the less-than-crisp image on the screen to be able to use it. In fact, that particular image is so blurry that I am not sure it can be used at all.

How about an actual sample of data in a file attached to the post? It'll help others help you.

 

[gemma-the-husky]

You sort in access in a somewhat similar to excel. If you want to sort by surname, then forename, you put these in columns C and D say, and sort by columns C and D.

Access is not too different. What is different is that you need a good understanding of what a database is, and how to use it. You can't just enter data as you can with excel.