Activate Button, Complete Button

[kiamba]

hi there,



I am working on a database for jobs what i want to be able to do is:

Activate button - when pressed the button it generates a job number so job is active
Job Complete button, - when pressed the button closes job and moves details to a job completed table/form

Please help if you can.

thank you kindly

[June7]

Programmatically generating unique identifier is a common topic. What structure should this identifier have?

Review https://www.accessforums.net/showthread.php?t=23329

Advise not to 'move' records, just set a field that flags record as "completed": CompletionDate.

[Micron]

If the job can have more than two status' (Active and Complete) then consider using Status and StatusDate fields.
To me, a job could have as many status values as
- plan
- submit
- approved
- rejected
- active
- fini
- complete
It all depends on the business process, I guess.

[orange]

kiamba,

I agree with the other responders and would recommend that you provide more information about your proposed "database for jobs".
What is the main purpose of the database?
What are the major processes that the database will support?
Can you walk us through "a day at the office" that shows a sample of what your database should deal with?
Basically we need more info in order to give more focused responses.

[kiamba]

i have attached the database hopefully correct,

I have an activate job, which i would like once activated will generate a job number (which would be like TDC7896 -
once the job is complete i press the job complete button and then that job goes to a completed table - that we can look back up when needed.

sorry novice, but if i dont ask for help i will not learn

[kiamba]

If anybody likes to do this for fun and can work with me to help design and make it work, i would be appreciative,

I know how i want it to work but just not the brains to make it all happen,

but i want to learn

[Micron]

and then that job goes to a completed table

A bit late here for me to get into your db, but no, you do not move records around like that. This has already been stated in post 2. If you want to learn, then you need to pay attention to what you're being told - especially from accomplished db people like June7. If you don't understand what you're being told, don't ignore it or you might find yourself short of people willing to advise you.

[kiamba]

okay thank you, i obviously dont understand enough

i will go exploring.

thank you for your time

[Micron]

Taking a quick look - don't go too far with this yet. Some table changes needed for sure.
It would help if you took the time to provide some background as to what process this db needs to support. Maybe walk us through the who, what, where, when, how in a typical day of operation. This will likely result in more questions as you cannot be expected to think of everything in your first attempt. F'rinstance:
- is a job one task you do for one customer or could there be 3 tasks in one job?
- Could it be that all the work is not done at the customer address?
- might work site address and billing address and customer address be different?
- could it be that the work is spread over different locations?

These and other questions directly relate to table design considerations.

[kiamba]

Thank you,
we are a traffic management company,
we draw plans get council permits , Transport Main Roads Permits and Queensland Police Permits when required.
so a typical job would go like this

create a job -enter details - generate a job number
go to permit tab put when permits have been sent, when they are received , once all is received and the traffic control is done(if required - that is booked in seperately_ I just want to be able to mark the current job as complete.
so i would like to have a form or table to show all current jobs, and all complete jobs.
i hope that makes some sense to you all.

thank you so much

[Micron]

You might think I'm asking too many questions but I'm just trying to understand what parts you don't cover need to be done by the db. That's just my analytical nature (consider it the price for free help
You wrote
create a job -enter details - generate a job number < asked but not answered: is there a number format, e.g. TDC on all jobs or ABC on others? What makes up that number? Is the next job after TDC7699 automatically going to be TDC700?

go to permit tab put when permits have been sent, when they are received < maybe down the road you'll want db to issue permit requests (print or email) but not now
once all is received and the traffic control is done(if required - that is booked in seperately < is traffic control stuff to be in db?

I just want to be able to mark the current job as complete. < if you're saying you just want db to generate a number and you update db when job is done and that's all you want db to do, then this will be much simpler than I imagined it might be.

I might have been editing post 9 when you posted. Maybe review that one.

[kiamba]

every jobs has TDC prefix for the job numbers. and increment by 1 for the next job
yes i want db to update job as complete and be able to look at all complete jobs in a table
and all current (open) jobs in a table.
each job is for one location, we can have many jobs current for same customer but at different locations so they all have their own separate job number
the site address will always be different to the customer detail address at the top (that address is the client office address)

ask away as i answer you i learn more each time.

[Micron]

Trying to watch ball game (Seattle/Houston) and not seeing much of it - I should have known that I'd get into this.
I'm going to park this for tonight and concentrate on the game. Here's a file of notes - see what you think. Maybe figure out what data types I didn't id. I hope you're the type who would see a term (e.g. primary key) and look it up rather than post back and ask. Also, some reading to bring your knowledge up a bit - especially on db normalization.

[June7]

@Micron, no file or 'reading' attached or linked.


"be able to look at all complete jobs in a table
and all current (open) jobs in a table"

Yes, that is what queries and filter criteria are for.

[Micron]

Trying to watch ball game (Seattle/Houston) and not seeing much of it - I should have known that I'd get into this.
I'm going to park this for tonight and concentrate on the game. Here's a file of notes - see what you think. Maybe figure out what data types I didn't id. I hope you're the type who would see a term (e.g. primary key) and look it up rather than post back and ask. Also, some reading to bring your knowledge up a bit - especially on db normalization. FWIW, I spent years in equipment reliability, which was very deep into preventive/corrective maintenance work orders (jobs), planning/scheduling WO's (work order) completion aspects etc.etc. etc. That's why I first posted that a WO could have so many status values. A WO could have up to 99 tasks on it in various statuses as well. So not to boast (too late?) but I think I can help here, but if you have to reign me in with what the db could/should do then feel free.

kiamba_tables.txt

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - https://www.isladogs.co.uk/multivalu...lds/index.html