add fields

[slyfox6915]

Hi i am working on an app that would be used for creating work orders

sometimes we have clients that have 1, 2, 3, 15 equipments for us to maintain

how can i tell access to create the amount of fields needed

i need the following

Manufacturer / model / KVA / S/N / Cabienets / Localasation

like i said some clients may have 2 or up to 25

on a form i would like to have a textbox that asks how many piesces of equipment to service

then it would create to needeed amount of lines to input all the infos


thank you

 

[HighAndWilder]

Are you wanting to add fields / columns in the table or add records / rows in the table?

 

[slyfox6915]

Are you wanting to add fields / columns in the table or add records / rows in the table?

good question

i guess i would need to put the desired amount of columns in the table i guess if i want to save it

i do have a table for work orders so some would have more equipement than others so what ever would be the best way to do this

 

[Micron]

You're trying to design db tables like spreadsheets - don't!
Research db normalization and see how related data goes into its own table(s) in rows/records.
Perhaps you'd have a work order header table, a work order task table and an equipment list table. Then perhaps junction tables but can't say based on what you posted. A typical work order db should have at least 4 tables just for the work order part, maybe more.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

 

[slyfox6915]

You're trying to design db tables like spreadsheets - don't!
Research db normalization and see how related data goes into its own table(s) in rows/records.
Perhaps you'd have a work order header table, a work order task table and an equipment list table. Then perhaps junction tables but can't say based on what you posted. A typical work order db should have at least 4 tables just for the work order part, maybe more.

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

i do have 4 tables currently

billing client, service client, tech (assigned) and WO

the last one would have the equipment into it

is that what you mean ?

 

[slyfox6915]

i do have 4 tables currently

billing client, service client, tech (assigned) and WO

the last one would have the equipment into it

is that what you mean ?

Thinking now you might be right i am thinking like excel let me look at those links you gave me

thank you in advance

 

[Micron]

No, I meant that just for handling work order data.

A typical work order db should have at least 4 tables just for the work order part, maybe more.

Could be as many as
tblWO, tblTask, (this is like PO and PO line items if that seems familiar), tblWO_Cmnts, (comments), tblTask_Cmnts), tblTaskParts, tblTaskLabour, tblTaskCost, tblWoCost, and maybe event completion comments tables for when a task (and perhaps a work order) attain Closed status.

Then there's tables for departments/divisions, personnel, documents (e.g. for related Safe Work Permit documents but in Access the file path and not the document itself), equipment, etc. It all depends on the flexibility and depth of data that you want out of your db.

 

[jackd]

Here is a graphic showing WORKORDER layout from a different request. You may get some insight from it.

 

[slyfox6915]

Here is a graphic showing WORKORDER layout from a different request. You may get some insight from it.
View attachment 56649

thanks

here where i am so far

Currently i have companies that can have multiple locations



They have contracts for some or each locations



Some contracts may include many pieces of equipment (we deal with UPS’s)

Each contacts have a WO # associated to them

Some contracts are 1, 2 or 3 years

Some contracts may have 1,2 or 3 minor preventative maintenance

Some contracts will have 1 Major preventative maintenance

Some contracts just include the maintenance, and some include parts

Then I need to assign the wo to a tech

Then I will need to build a form that will have a checklist for the tech to fill

• Form will be different depending on minor or major maintenance
• Tech will return the form then I will need to transfer that to a report that will be sent to the client (with graphics and such for the batterie voltage)
• Also include pictures that tech would include in his report

I think this is about it



So currently have a table for



1- WO table: that will need to have billing info, service address, wo#

2- Equipment table: equipment with manufacturer, model, serial number, number of battery cabinets and localisation and amount of KVA.

3- employee table: name of tech and contact infos (phone and email mainly.)

4- billing contact: company name, address and contact name and number

5- service info: company name, address and contact name and number



Might need more tables but this is what I have so far

Will need to check out relations between the tables as 1 billing contact can have multiple service contact

1 billing contact will have multiple wo#

Wo# will have multiple equipment



And so on ☹

Am I in the right direction ?

Please note I do not need and invoices or anything of the sort as I have my accounting software taking care of that (acomba)

 

[jackd]

The graphic I provided was intended to give you some idea of the tables involved in workorder processing. Your business may differ and some table(s) and relationship(s) may not apply. You may need addition tables and relationships to reflect your business processes.

You should build a data model of your business so that you can test and vet the model with some sample data and ensure that the model supports your business and related transactions. See stump the model for some ideas.

From an Access view you should not use names that have embedded spaces. Test your model - it will become the blueprint for your tables and relationships.
Good luck.