Creating Bill of Quantities 1

[khwaja]

I am comfortable using relationships but recently when I was asked to create a bill of quantity system for various properties I was knocked off.

We have a long list of equipments (some 1600 items) which need to be costed and quantities identifies for individual properties. It is relatively easier to create a master spreadsheet of equipments and use and re use that to create bill of quantities but in terms of Access I cannot get my head around how to capture this so that we have an equipment list to maintain and when necessary take some records (using flags) and save them for properties together with quantities and prices for equipment.

Will appreciate if someone has come across a similar problem before or have created something like this to help me with this.



Cheers

AK

Note: Using Access 97 - still.

 

[KenReay]

Hi

In the classic Bill of Materials for Engineering and similar products you have a Parts Table and a "UsedIn" table

The skeleton structure would be

tblParts
strPartNo - PK
stRDescription

tblUsedIn
strParentPart ) PK
strChildPart )
lngQty

This structure will allow you to represent situations where an item is made of of assemblies which are themselves made up of subassembles, which are made up of parts etc

I suspect from your description that your situation is more somple in that you have only a 'single level' list, so given the little information you gave in your post I would say the likely table structure would be:

tblPropery
PropertyId (PK)
PropertyDesc
...etc

tblEquipment
EquipmentId (PK)
EquipmentDesc
PropertyId
...etc

for 'spare' or unallocated items you could have a dummy property to which such 'spare' items are allocated

Hope that helps


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[willir]

khwaja

Another variation, depending on your needs is the employee and boss scenario. Each employee has a boss. The boss is an employee and has his own boss.

Here, with parts, that is made of parts, has a parent id. For example...
A Truck with a snowplow. The parent of the snowplow would be the truck.

The snowplow is made of parts including a hydrolics which is tracked in the database. The parent would be the plow blade.

I think this may be similar to Ken's decription, except I have seen the parent key stored on the parts table. The difference is that Ken's answer allows for a many-to-many relationship, and this scenario supports a one-to-many relastionship.

Richard

 

[khwaja]

Thank you both for the insight. Both approaches are good but the only difficulty I have is the cumbersome need for a user to create a bill of quantity (or survey as we call it) from scratch each time they have a new property based on design plans. I am sorry for not being detailed previously but I will attempt to do it now.

We have a number of stores nationwide. Each of these gets refurbished periodically and that entails installation of new equipment. We have a bank of equipment, which currently sits in an Excel spreadsheet. We are now trying to move away from Excel and create surveys for stores in Access so that the person responsible for maintaining these equipment does so on line and those responsible for creating surveys get the quality info when creating a list of equipment required together with quantities.

There will always be one store that may require a large number of equipment to be installed, which can be divided into depts for convenience sake. Normally, I would expect the user to select a store and then start creating a new survey by adding equipment. However this becomes quite tedious (as compared to Excel approach) because you have to select nearly 650 equipment items each time for a new store. Technically it is feasible but not convenient. Taking either of above approaches, I can create the system but unable to provide the user the functionality to have oft-used equipment pre selected. In other words, when picking up a store I should be able to see at least 650 records to begin with whose select can be modified using a separate screen. Naturally, I would like to be able to record the equipment for each store which may require store FK against each equipment item and I cannot figure out how could this be achieved? Even if I manage to create and run this system, it is likely to inflate the database when we have 700 stores to process. So my gut feeling is that I may not be thinking in the right direction. There has to be some other approach. Therefore, my question was to seek some help as to how does a classic system works when you have large number of equipment items to be assigned to one store and you might want to save a snapshot of survey if not as an on line record?



Cheers

AK

Note: Using Access 97 - still.

 

[KenReay]

Hi

Why not just have one or more 'standard' packs defined and have a facility to copy them in with a single click

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[khwaja]

Thanks Ken. Are you saying that we can create a kit which can be associated with the store? If so, I would still need to be able to add/delete one or two new equipments which I am sure can be managed. But the important part is how to keep a snapshot of BOQ in relation to the store it was prepared. You mentioned a click to do that, would yu be able to shed more light on this as I have also been thinking about this likely solution.



Cheers

AK

Note: Using Access 97 - still.

 

[willir]

AK

Access 97 is not bad at all.

I created something that sounds similar to your needs. Basically, we needed to create a quality survey on computer or server work. It became cumbersome because varies parties had ownership of the specific task - hardware, network, applicaitons, seucirty. Some of the info for the survery was redudent, some not relevant. The database I created allowed for assigning taylored surveys depending the specific requirements.

How does this compare to your needs? Well, a product "profile" would preselect xx number of products. The store would select the appropriate profiles for their needs.

How my database worked was as follows.

tblStatement
[red]StatementID[/red] - Primary key, autonumber
+ statement details, ie question, category, etc.
- This would be akin to your product

tblStatementProfile
SProfileID - primary key, autonumber
[blue]MProfileID[/blue] - foreign key to tblMasterProfile
[red]StatementID[/red] - foreign key to tblStatment
+ specifics to the statement profile - last updated, line number on the survey
Primary key - ProfileID + StatementID
- This is really an intermediary or join table between the statement profile and the statment tables.

tblMasterProfile
[blue]MProfileID[/blue] - autonumber, primary key
[green]MasterID[/green] - foreign key from the Master table
+ specifics on the profile - last updates, sequence number
- This is really an intermediary or join table between the profile and the statment profile tables.

tblMaster
[green]MasterID[/green] - primary key, autonumber
+ specifics for the master table - description, decription code, owner

What these three tables give you...
- Statments can be grouped together to form a profile. In my case, 7 statements for dekstop "prefilight"; 5 statments for desktop security, 15 statements for quality checks before releasing to the customer.

In your case, this would be the product name would equate to the "statements". The statement profiel would equate to a minor product grouping.

Kids high end skis subprofile,
- Atomik mogul master
- Phead downhill extreme


kids low end skis subprofile.
- Atomik alpine
- Phead downhill special

Adult high end skis.
- KX2 XX9
- Atomik Phatom


Adult Low end skis.
- Phead EasyTurn
- Atomik Special

- A several statement profiles are linked to a master profile. In my case, rebuild a laptop, roll out a new desktop. Build an NT server. Some of the statement profiles would be reused such as the "preflight" work.

In your case, this could apply to a main product groups.

Kids skis profile,
- high end kids skis
- low end kids skis

Adult skis profile.
- high end adult skis
- low end kids skis

- Lastly, the master table

Skis
Shoes
etc


On the complemetary side, the store has to choose their profile

For my survey database, this was done using two tables...


tblCall
[blue]CallID[/blue] - autonumber, primary key
+ details on the call
This would be your order table


tblAnswers
AnswerID - autonumber, priamry key
[blue]CallID[/blue] - foreign key point to tblCall
SProfileID - foreign key from tblStatementProfile (way near the top)
+ answers
This would be the equivalent to the order detail.


For example...

0 - 150 cm Atomik mogul master
25 - 160 cm Atomik mogul master
25 - 165 cm Atomik mogul master
0 - 170 cm Atomik mogul master
15 - 180 cm Atomik mogul master


Basically, the store would select the high end line for kids and adult ski profile, but not the low end lines. Code would populate the details. The store would then indicate the quantity for each model in the profile.

Since you are working with orders and such, instead of, or in addition to, you may need to include the detail product number on the answer table. I have only indirectly referenced it through the tblStatementProfile. This suited my purposes, but the risk on your end is hat some one may change the profile.

On the reporting side, inforamtion can be gathered on number of skis, high end vs low end type stuff at the high endd reporting down to the nitty-gritty details.


I realize this may seem complicated (or perhaps not). By using two layers of profiles, it allows the detail items to be assigned to different groups. If it did not give you a solution, I at least hope it gave some ideas to pursue.

Richard

 

[khwaja]

Richard, thanks a lot for the detailed instructions. I would have to read this time and again and experiment before I can even raise a question but the concept is intersting. I may not have more than 2-3 profiles but concept is there. I will try to work on this and will get back to you. Much appreaciate mate.

Cheers

AK

Note: Using Access 97 - still.