Simple lookup on a form

[James_liv]

Hello,

Hoping someone can help.


I wrote a database in Lotus Approach 20 years ago and still use it almost every day for my small retail business.

It's a bit long in the tooth now and has some serious compatibility issues with Windows 10 - it's also very slow - so I decided to rewrite it in Access.

I've got stuck at the first hurdle and after watching hour after hour of tutorial videos on YouTube I'm still struggling with one very basic element.

On the first form I have the usual customer address, door number, name and postcode and I want it so when I type a postcode and door number, if it matches one already in the database then it populates the name, address and phone number fields on the form.
If there are no matches then they remain blank so I can tab through, populate them and obviously when it saves it adds them to the database for the next time I input those postcode and door numbers.
Surely it can't be that hard?

Can anyone help with some very basic and simple instructions please.

I would be extremely greatful.

Many thanks
James

[Missinglinq]

To do this sort of thing, you have to be referring to a unique piece of data, or a unique combination of pieces of data. How can a postcode plus a door number be sure of providing such a combination? At the least, you would need an address, door number and postcode, would you not?

Then you could use this combination in the DCount() function to see if the combination already exists...and if it does, retrieve the Record...rather than just populating a new Record with the named fields.

Linq ;0)>

[orange]

Getting your tables and relationships designed to support your business facts/rules is critical.
Suggest you show readers your tables and relationships and a brief overview of the purpose of the database.
That is, your business description is simple,plain English.
Forms will come later. Use some sample data to make sure the tables can store data and that you can derive the data you need from those tables.

[James_liv]

Thank you for your replies.

Yes, the postcode and door number would be unique to that individual address.

I don't have any forms yet because this is literally the first thing I need the database to do and so I am stuck at the first step.

I created a table with postcode, door number, name, address and phone number.

I put those into a form.

The first field says postcode and then door number. At which point I need it to populate the name and address field with the rest of the details but I don't know how to setup the relationship so it will do this.

Lotus Approach made this step extremely simple.

However, if I could so much as have a unique order number that pulls through a matching fruit, book, anything as an example and I could potentially copy that method into my form.

My small business is the delivery and sale of whitegoods but at this stage it makes little difference.

Again, thank you for your patience and help.
It is appreciated.

[Bob Fitz]

James

Take a look at the attached db which shows 2 ways of doing what I think you want to do. Post back if you need any clarification.

However, I would advise that you take on board any advice offered by Orange or Missinglinq. I consider them both to be amongst the best this forum has to offer.

[orange]

James,

Have you reviewed Google for info on Converting Lotus Approach to MS Access?
https://www.google.com/search?client...h+to+ms+access

Most suggest you export Lotus info to csv files for subsequent import/usage of your existing data with MS Access.
https://answers.microsoft.com/en-us/...0-bc1917232aae
But the key is to database design is to review your business processes and ensure your tables and relationships support your business facts. We can help you, but we need some facts/materials on what you are working with.
Good luck with your project.

[James_liv]

Thank you for your replies.

I thought it might be easier for people to help if I put together the start of the database and then explain my problem.

Please find attached the start of my booking system. Sorry, I had to zip it because it was 576kb which is slightly above the permitted attachment size.

The data in there is obviously just made up test data.


The first issue I have it that for some reason I am unable to type into my form which obviously is essential.

Now, my main request for help.

You can see that the first field that you tab into is the postcode and then the door number.
On a new record these would initially be blank and you would enter a post code followed by a door number.

If these combined match another record in the tblCustomerInformation table then it should automatically populate the rest of the address and contact number fields.
If nothing matches then I should be able to continue tabbing through these fields entering the relevant data as I go and it would automatically save these address details into the tbhCustomerInformation table for the next time.

I am really struggling because this seems such a basic concept. Clearly my understanding of Access is lacking.

Could someone help me please.

Kind Regards
James

[orange]

Please tell us a little about your business? You have employees, you have customers, you sell "whitegoods", you have bookings, you make deliveries, you repair whitegoods?...... Perhaps you could just adjust/connect these to provide the gist of your business.

Is this what you mean by "whitegoods"? (new term to me)

white goods. Heavy consumer durables such as air conditioners, refrigerators, stoves, etc., which used to be painted only in white enamel finish. Despite their availability in varied colors now, they are still called white goods.

[James_liv]

Hello again,

Yes of course.

The business sells and repairs kitchen appliances through both a retail outlet and to student accommodation.

Some people may purchase fridges, freezers, washing machines for eg and pay over the counter or on credit.
Other people will phone and request the repair of one of their kitchen appliances and we book an engineer to their home.

We have 8 employees. (3 engineers, 2 delivery drivers, 3 sales staff and myself (although I actually work full time for another company and just help out)) .
It's my families business and they aren't very computer literate hence me trying to help out.

I built the system in Lotus Approach 20 years ago and it's not really me who uses it so often. It's the people in the shop and on the phone and so it's important I keep it as much like the old system as possible.
I tried to rewrite it once before and became stuck on something very similar then too but now the old system has just become unusable for lots of reasons.

I hope that helps but if you need anything more then let me know.
If you see the first part of the database I uploaded and comments on it then you should be able to see where I am struggling.

Many thanks
James

[James_liv]

Sorry, double post.

[orange]

I had to repost this because the original was garbled with a bunch of html tags and fragments???????

James,
Are you still running your Lotus Approach system at this time? Do you have any pictures of the tables an/or forms you were/are using? Please post (zip) whatever you can share.

I looked at the database you sent. There are other entities that are important to your business and should be addressed in your design.

Here is a generic data model from Barry Williams' site- showing the typical entities and relationships in an appliance repair business. It may be more detailed than you need, or may have to be adjusted to match your particular business. But it should give you some idea of the things involved and how they are related to each other.

I recommend you start with a description of your business -start at the 30,000 ft level and gradually add details. Review it with others to ensure what is described represents what is actually done. It's important to get the scope of the business understood and to identify the things involved.


Here is a list of steps that many follow when designing their database.

I would advise you not to start with a physical Access database that you intend to extend and revise as new pieces are encountered. That is a long, frustrating approach.
Build a model(data model) based on your description. Create some test data and test scenarios. Then play "stump the model". You will end up with a blueprint for your physical database.


Good luck with your project.

[James_liv]

Thanks, I appreciate the help.
Yes I still have the Lotus database and I'm happy to send some pdf's of the forms but the shop is closed today.

I will copy the design of my approach database fairly closely and so feel I'm kinda beyond that stage.

What I really need to understand is why I am unable to enter data in the one I have uploaded.
I out this together to show my main issue which I need help with.

I also need to know how to make it so when I enter a postcode and door number it populates it with the address.

Two things. Once I've done this I will move on with my re-write and come back to update as I encounter further issues or solutions.

Can anyone advise how I solve the two things above please?

As always, extremely grateful for your help.

[orange]

James,
As 'linq commented, you have made a poor choice of PK for your tables. The PK should have no meaning to you--it's sole purpose is to uniquely identify each and every record in that table. I would recommend that you have an autonumber Primary Key in each and every table you build. Spend some time on database design concepts. Do NOT be too quick to jump into any physical database.

The articles in the Database Planning and Design link in my signature have been reviewed and tested many times. I think you would find the tutorials from RogersAccessLibrary very helpful. To work through a tutorial would take about 45 minutes and you will learn some concepts suited to your new database.

For Access specifically, do not use names with embedded spaces. They will come back to bite you. I would advise NOT to use lookups at the table field level. Use a separate Lookup table.
Again, good luck.

[James_liv]

Does the PK not need to be the unique relationship point between two tables?

I do have order number and customer number on both but I assumed that in order to create a working relationship between the two tables then this had to be the PK.

Bearing in mind that the combined door number and postcode is complete unique to that customers address.

Thanks

[orange]

I have made copies of your tables and adjusted same.
Here is graphic showing relationship of Customers/Bookings/Employee who Booked



Here is graphic of Customer comments.


Some comments on Booking table


But there are other parts to be considered that we don't yet know about.

Products, Suppliers/Vendors, Payments, Credit, Orders, OrderDetails, WorkOrder, Invoice

Perhaps these weren't handled in your Lotus Approach, but it's hard to imagine that these were not part of your business.
James, you know your business better than anyone reading this thread. So you have to decide what is the scope of this conversion and plan accordingly. I strongly advise against -design as you go.

Again, good luck.