Noob - Need help, getting frustrated at failure :(

[Zombai]

Hi Guys,



Intro:
I've only just jumped into using Access, and i'm trying to create a database and forms for my father's mechanical business.
I'm an IT guy, trying to get this old man out of the stone-ages of pen & paper by digitalising everything for him.

My goal is to use Access to keep records of customers, their previous jobs, and automate the creation of job cards.
I originally started to do this using Excel, but found that Access would have greater functionality for me.

Unfortunately, i have been trying and been failing. I've scoured the internet for information to try and help me achieve my goals, however the information i've found assumes i already have some knowledge.

In a perfect world:
First thing i'd like to be able to do, is create a form that will auto generate fields from a table (phone numbers, address etc.) when selecting a customers name and auto generate a job number.
If a customer doesn't exist, i'd like to manually fill out the form and select a 'create customer' button to save the new information into the table (i'll worry about editing existing customers later).
During this, i would like to have a combo box, or a list box, that has the selected customer's vehicles in it (which is stored in the customer info table), where i can select from multiple vehicles exclusive to said customer.
I'd like to be able to look up information from the customer's most recent job on the specific vehicle, saved in a 'Complete Jobs' table to display repairs that are required.
Finally, once the above is completed, i'd like to have another button to 'Generate Job Card' ,export the information to an excel spreadsheet(or another access form, whatever is easier) and create an entry in a 'current jobs' table.

The mechanics will then fill out the excel spreadsheet or access form, and once done import that data back into Access's 'Current Jobs' table. When the job is complete, it is removed from the 'Current Jobs' table and placed into the 'Completed Jobs' table for archiving and the 'required repairs' reference.

To add to the difficulty of my request, i'd like to have the possibility for two different customers be referenced during the customer lookup. Couples/Families will often bring their vehicles in and share a billing account.
I'd like the capability for Hubby to come in and drop off vehicle 1 and the wife to drop the vehicle off the next time, and still be able to look up the same vehicles.

In Reality:
I know i'm essentially asking someone to do all this hard work for me. I'll be able to learn from anything that's supplied, and would appreciate guides even more.
It's a big request, and i wouldn't be posting on the forum if i didn't need the help.

Screenshots:

Example Form


Current database values for 'Customer's database.

[ssanfu]

I would suggest taking a step back and think about your problem. It sounds like you have jumped into Access creating "things" without doing any planning/designing.

Start by reading this link , Post #7.

It would be best to start with describing the problem(s) you are trying to solve. Without a clear understanding of the end result, you are going nowhere fast.
Then use the drawing board (or white board, window, paper and pencil, cardboard,....) and design the table structures/relationships. Manually walk through a few record entries to see if you can break the design.
Once the table structures are done and the relationships are understood, then you can open Access, create the tables and begin developing queries, forms and reports.


Post back here with a description of the problem and your table designs if you want feedback. Many posters here are superb on table design/relationships.
Ask questions if/when you get stuck or need a little help.


Your project is not insurmountable, but only you fully understand where you are headed. We are like the blind mice describing an elephant.


PS Welcome to the forum..........

[June7]

Ssanfu, oops, your link is this thread.

Zombai, have you spent any time studying an introductory tutorial book to learn the basics of relational database principles and Access functionality? An understanding of programming concepts is also recommended. If you want to 'auto generate' a job number as a unique identifier that has meaning to users, this will require code.

[Zombai]

I would suggest taking a step back and think about your problem. It sounds like you have jumped into Access creating "things" without doing any planning/designing.

Without coming across rude, i have thought about the problem, and have planned it. I mentioned my plans and problems - although i may not have communicated it as clearly as i thought i did.

My problem is, simply, i don't know what i'm doing lol.

Once the table structures are done and the relationships are understood, then you can open Access, create the tables and begin developing queries, forms and reports.

I don't exactly know how to develop queries/reports or properly configure forms.

I understand that explaining all of the basic stuff like that is remedial, and stuff that i should already know before trying more complex features, however i learn best by jumping into the deep end. I will pick up the basic stuff quicker, if i am attempting to learn complex stuff.

Zombai, have you spent any time studying an introductory tutorial book to learn the basics of relational database principles and Access functionality? An understanding of programming concepts is also recommended. If you want to 'auto generate' a job number as a unique identifier that has meaning to users, this will require code.

As stated just above, in this reply, i learn the basics quickest by attempting the complex stuff. I know some very, very basic database stuff from playing around with MySQL with some MMORPG private servers.
I have an intermediate understanding of programming and coding, and can generally edit coding to suit my needs - edited some codes for Excel macro's for my needs. I've found an snippet from another site for doing the auto generation of job numbers.


Essentially, if i could be told what needs to be done, to achieve my goals, then i can figure out the specifics for it.

I.e. Make 3 tables, have table x relate to table y using this relationship, then script this to do that. Create a form with this, this and this in it.

I dont know even know if the above example makes any sense, but i tried - lol

[June7]

Don't know your business processes well enough so any structure I suggest could only be brainstorming ideas. Identify data entities and how they relate. Draw an Entity Relationship Diagram (ERD) if that helps you organize. Build tables to fit. Then work out the user interface. What you've posted provides no structure for us to analyze. All the processes you describe can be done but the how is really too much to cover in a forum thread, especially since there is always more than one way to accomplish something, and we don't know your db structure.

I will suggest that having a 'Current Jobs' table and a 'Completed Jobs' table and 'moving' records is poor design. Whether or not a job is 'completed' should be determined by calculating data or setting a field value (like a date of completion or a Yes/No). Then apply filter on that field to exclude the 'completed' from display in form or report.

And why would you need to export to Excel? Why even think about that at this stage?

Perhaps the MS Services and Workorder Management database templates would be helpful. I haven't looked at them but the titles are intriguing.

Did a Bing search - access database automotive repair: https://sourceforge.net/directory/os...epair+database - 4th item down "Repair Center"; http://www.accesstogo.org.uk/Service..._Database.html

[ssanfu]

Ssanfu, oops, your link is this thread.

Should have been https://www.accessforums.net/showthread.php?t=65906 Post #7
(stupid computer! Why don't you do want I want, instead of what I tell you to do??)

Without coming across rude, i have thought about the problem, and have planned it. I mentioned my plans and problems - although i may not have communicated it as clearly as i thought i did.

No offense meant or taken. It is just that there is not very much info about your dB tables/relationships.

For instance, you said

To add to the difficulty of my request, i'd like to have the possibility for two different customers be referenced during the customer lookup. Couples/Families will often bring their vehicles in and share a billing account.

Reasonable option - makes a lot of sense. But HOW are you going to do that? It depends on the tables.relationships...... which is not stated. One way would be to look up the vehicle license plate number.... but apparently you don't record that info. Another would be to have a junction table between the customers and the vehicles. But don't know if you have that design.....

June provided a few links. Here is another that might help http://databaseanswers.org/data_mode...nter/index.htm


Again, read Post #7 in the (corrected) link in this post starting from the line: "(also note: "ID" is POOR name for a field and an Autonumber field should never be visible on a form/report.)"


If you do have tables created, it would/could help if you would post your dB. Then we would have a little better understanding and could make better suggestions.