Full Version: Access 2010 Forms
UtterAccess Forums > Microsoft® Access > Access Forms
Jrod7287
this is my first time creating a database.
its for a small business, and what id like to do is under each "customer contract", create a "NOTES" subform, with a button adding a new note, including date and time.
pretty much to keep track of service calls to each individual customer
im sure this is a pretty easy thing to to, but im stuck.
can someone help me please.
GroverParkGeorge
Welcome to UtterAccess.

First, while you've described in general terms your desired outcome, there's not enough detail present to do more than suggest some broad concepts to follow.

Before you begin to think about forms, you must design and create tables to hold your data.

You'll need at least the following tables:

Customers
Contracts
Service Calls
Employees
Notes

How many more tables you need, and what fields you need, will depend on knowing a lot more detail about the business.


We have some good articles here in our New Comers Reading List. Studying them will help you get started with your project.
Jrod7287
Hi! Thanks for replyiing. .we are a solar And electric company. We install solar panels for a client, and the database is pretty much used to keep track of each job we have during the install, inspection, and power-on process. I did create forms and tables for all our customers(address,#, type of roof, status of job) contract prices, our employees, their pay rate (eventually I'd like to add an expense report to keep up with the profit and loss per job) And I started making a table for the service calls for each customer. Some customers failed inspection, and require corrections, some have been completed but may have a leak, some haven't been installed yet. I have a note field on the contract page, but Its pretty much just a blank field on the table that I kind just type as I go.
Is that enough information? I'm sorry, I hope I didn't confuse you.
GroverParkGeorge
That's much closer. Thanks. It's not a matter of confusion so much as needing adequate information to understand what you are trying to accomplish.

Which table does the note field belong to? Again, forms come second, tables have to be set up properly first. It's sort of like building a structure, or installing solar panels, in a way. You need a solid foundation for a building, or a solid structure to which to attach the solar panels. Tables are the building, forms are the solar panels, if that's not stretching the metaphor too far. No point on putting good panels on an unsound or inadequate roof system.

So, you have a note field in one table. Which table is that? How does it relate to service calls, if at all?
Jrod7287
Its on the customer information table. Reason why is because when I started making the database I exported everything from an excel spreadsheet, showing all the customer info, and when was the last time the customer was contacted, or the latest note regarding that specific job.
what I'd like to have however is a...subform perhaps, that I can just click "new note" and for example put, "spoke with cus timer today, complaining about leaky roof, sending John to check it out"(with a date stamp, employee id, time stamp) and once the service is done I can add another note (containing date stamp, employee id, time stamp) stating "John went to patch up holes. Customer is happy" and if I were to look up that customer, on the customer information page, it would have every note in chronological order.
This is just an example, but that's the best way I can explain it.
I'm unsure how I would go about this -.-
GroverParkGeorge
Given your requirement, then, I don't believe the notes field belongs on the customer information table.

Let's think about it. You describe multiple notes per customer. That is, in table design terms, a "One-to-Many" relationship. One customer --> many notes. Such situations require two tables.

As I would set this up, then, the customer information table would have fields like First Name, Last Name, billing address and service address, contact phone, contact email and so on. However, and this is critical, you could ONLY add one note per customer if the note field is in this table. That appears to run counter to your description, where you mention TWO notes, one before and one after the service call. Moreover, you want to capture the employeeID of the person making the service call. Not possible here either.

Instead, I think what you need is a separate table for notes. It would have a memo field for the note itself, plus a foreign key field that links each note to the appropriate customer record, along with a NoteDateTime field to capture the date and time of the note and a foreign key field to the employee table, so you can capture the employeeID of the relevant employee. This allows you to capture those multiple notes you describe.

Also, with this table design, it is then quite straightforward to create your subform for Notes!
Jrod7287
wow...
I don't know why that thought never came to mind.
thank you.
im sure I can figure this out from here.
GroverParkGeorge
Good luck with the rest of your project. Keep us posted on your success.
ScottGem
QUOTE
I don't know why that thought never came to mind.
thank you.
im sure I can figure this out from here.


I'm not so sure. You said earlier:

QUOTE
pretty much to keep track of service calls to each individual customer


But if that's what you want to do, you should have a calls table. It should be more that a free form Notes field with just a datestamp. You should have fields for the CustomerID. the time of the call, the type of issue raised, the employee dispatched, when the call was resolved and 2 Notes fields. One to describe the problem, the other to describe the resolution. Maybe more fields as I don't know what you need to capture for each repair call. You might even need to split this out to a Call tracking table. So you have the original call as a parent record and a child table to track each service call made with respect to the parent record.
Jrod7287
ok, I created a table and form for just the notes.
fields are for employee id, date, time
and the memo field for the note.

I created the one to many relationship with the customer Id linking the customer info form and the notes subform,
I also have a button to add new note and a button to save the note.
However when I save the note, and then go back to add a new one, it saves it as pages.
I want it to be saved as a chronological list, of sorts, but id like the layout itself to be repeated, I guess like a copy/paste type thing?
im not sure.
am I making sense?
is this possible?
please help!
ScottGem
I'm not clear what you did, since you didn't follow our instructions exactly. Can you upload a copy of your database so we can review it?
dmhzx
Hope you don't mind me jumping in, but I've just put a quick db together with 'Comments Tracking in place.

First decide to which other 'entity' (or entities) the Notes belong to.
Do they belong to
a customer
an installation
a salesman.


That determines what Foreign Key field you need in the Notes table, along with the date, the notes themselves, and an Autonumber PK for the notes.

Your main form will be bound to whatever the table was from the first question, and the subform bound to the Notes table (or a query over it if you want the notes in chronological order).
I normally set the subform to continuous forms. - But datasheet is common.
After which set the Master and child field properly and you're just about there.

NOTE: It won't be 'saved' as a list, but it will look like one on the sub form.

If you want to export all this back to Excel, with all the comments in a single Cell ,post again if you'd like some tips.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.