is there a way to have 2 dates under a customer's name in Microsoft access

[Lasereyes24]

I'm an intern student at a company that does both wiring and aircon services. The job that they gave me was to make a database for them. I don't have any experience in anything related to databases.


So, I started to look up videos and stuff to at least learn a bit about databases and make something that works and I made it after 1.5 months of learning.


in the database that I created,


I have 1 table (CustomerDetailsT):


CustomerID (pk)


CustomerName


PhoneNumber


Address


Aircond (type and model of ac,ex: WM daikin 1.0HP)


AcDetails (what has been done for the ac.)


Others (yes/no) (Wiring, installing a fan and so on)


WhatHasBeenDone (shows what has been done for others)






Then 3 queries (CustomerOthersDetailsQ, CustomerAcDetailsQ, CustomerDetailsQ).**CustomerAcDetailsQ** has *CustomerName, PhoneNumber, Address, Aircond* and *AcDetails*. **CustomerOthersDetailsQ** has *CustomerName, PhoneNumber, Address, Others,* and *WhatHasBeenDone*.**CustomerDetailsQ** has *CustomerID, CustomerName, PhoneNumber* and *Address*


And 1 form with 3 subforms.
it's a search form, which would search for customers as we're typing in their name/phone number and it will show what has been done for the customer.


With this, I have created what the company wants, but now they want to add dates. Dates which would show when we have done something for a customer. Dates for Aircond and the Others stuff.

for example, Customer A needs the ac in his house to be repaired on 3.4.2022. So, this info would then be updated in the customer details table and then it would show in the customer search form. After that, Customer A faces a problem with his ac again and this time its the wiring for the ac and he wants it to be done on 5.5.2022.


I've tried with what I know and it didn't work. tried searching it on youtube and google, but still couldn't find it.


how can I go about doing this?. I have tried having separate tables for each service, but it became a hassle when I wanted to create a new customer...






[1]: https://i.stack.imgur.com/mtrmC.png [The Customer search form] [1]: https://i.stack.imgur.com/A3Y9d.png [example of a customer that has ac installation] [1]: https://i.stack.imgur.com/dsGL5.png [example of a customer that has both ac and wiring done]


I've posted the same thing in stack overflow and was told that the question is suitable in stack overflow and that access forums would be the better place so i came here.

[ArviLaanemets]

You can't do this effectively with a single table! As a minimum, you need:
tblCustomers: CustomerID, CustomerName, ...;
tblServices: ServiceID, ServiceName, ...; (this table defines all services like e.g. Aircond, Wiring, etc.);
tblCustomerServices; CustomerServiceID, CustomerID, ServiceID, ServiceDate, ...

[Lasereyes24]

is there anything that I need to do/that needs to be done to database besides changing up the tables?

[mike60smart]

As previously stated you need to change the design of your database.

1 Customer can have Many Services which means more than 1 table.

You need to bite the bullet and start again.

[orange]

is there anything that I need to do/that needs to be done to database besides changing up the tables?

Yes. You have jumped over a major step --design.
If I can make an analogy- someone has said build me a house. Your background gives you familiarity with a 1 room log house. And you have built it to the best of your ability and knowledge. Now,after you have built the log house, that someone says -oh the house needs 4 floors and 6 apartments on each floor. We need room for 75 people. Sound familiar?

The key is to get a statement of requirements. What is really required and expected? You need answers to questions like who, what, where, when, why, how much and how often to identify the scope of the project/database and its proposed usage.

Here is a link to a few tutorials from RogersAccessLibrary. If you work through the tutorials you will experience the process of designing tables and relationships to support a business process. Each tutorial should take 45 min to 1 hour. Each tutorial has a business definition, a problem statement, and solution.

For practical experience / learning of tables and relationships spend 45-60 minutes and work through one or two of these tutorials from RogersAccessLibrary.
ZYX Laboratories ***recently added
Class info system
Catering Business
Widgets