I posted a question here last week about an ordering database I'm trying to set up, and got some excellent help. So I'm hoping some of you might be able to get me on the right track with another question I've got on the same project.
Here's some general info on what I've done so far (For the record, I'm quite a novice at this.):
The database I'm setting up is for a small electric utility company to submit orders to the inventory warehouse for the parts that will be needed to build each project. The user enters the work request number (that comes from an unrelated work order system), and then can enter any number of materials orders for that project. Right now, the item number and quantity is entered by the user. I have a table that is imported from an excel spreadsheet from the inventory warehouse that has all of the item numbers and descriptions, and when the user enters the item number, it populates the description on the form. There is a query based on the current order number, and a report based on this query which can be submitted to the warehouse. All of this is working beautifully and it's doing exactly what we need... except now I've been requested to add a variation on this and that's where I need assistance.
When we are going to build a new powerline, each pole has a poletop configuration classification, or "unit number". They would like to be able to have the option to order by unit rather than having to enter each individual item specifically. For example, a C1 unit would have one crossarm, 3 insulators, and 3 pins; where a C2 unit would have two crossarms, 6 insulators, and 6 pins. Ultimately I need to have an order form that would show all of the individual components and required quantities to submit to the warehouse. So if they entered a quantity of five C1's, I'd need the report to list 5 crossarms, 15 insulators, and 15 pins.
I hope I'm explaining this question well enough. I didn't want to get too bogged down in the electrical terminology. Any suggestions in general on how to set this up? At this point, I'm just looking for some general suggestions on table/query structure.
Thanks so much,
April
15 2251
The question to ask here is whether you want to be able to order by unit only, or also by individual item number mixed together.
Do you need to record the units ordered, or can you just use them to quickly enter orders, and save only the item totals?
It seems like either way you will need a table with:
UnitNumber - text, PK
CrossArmCount - integer
InsulatorCount - integer
PinCount - integer
OtherPartCount - integer
etc. for all parts
Hi, ChipR.
The table could be "more normalized". ... IMHO
Kind regards,
Fish
They are okay with having the option of "order by part number" where all items within that order are by part number and then a separate option of "order by unit" where all items within that order are done by unit number. As long as the two different orders can still be linked back to the project. For instance, the guy who does the metering portion may want to order his stuff by order number, where the guys who build the line would want to order by unit.
These are my initial thoughts:
Table: tbl_unit_items
Fields:
Item Number
Unit Number
Qty Per Unit
Table: tbl_units_ordered
Fields:
Order Number (auto number)
Unit Number
Qty of Units
Then I thought I would set up a query with the two tables and then multiply the qty of units times the qty per unit for each item in the unit.
Is this even close?
Looks perfect in a case if unit items collection will not be modified once defined.
Sounds like that should work, and I think that's what Fish meant by "more normalized."
It seems the obvious solutions like to evade me late in the day.
Hmm, your requirement is a fairly tricky one to implement in a fully-general way. If at all possible try to keep table designs general enough to cope with other assembly groupings, not just one type, which perhaps is the disadvantage of Chip's suggestion. The table structure suggested is not fully normalised - which is OK if you can trade the disadvantages that lack of normalisation may bring for the simpler implementation that it allows.
In a generalised solution, what you are implementing can be either a higher-level grouped assembly - in which case you need to have another table with a many-1 relationship to reflect the grouping - or a self-reflexive one where a part can be a member of another part using a 'comprises' 1-many relationship on the same table.
Either way, dealing with such a grouping complicates selection procedures, presenting users with parts but also with assemblies comprising of parts - sounds simple but is far from simple to implement.
PS hadn't seen Fish's post and the subsequent replies when I drafted this one!
-Stewart
Are you saying as long as the group of items that make up a unit don't change? I can't really see any reason why they would change, but I suppose there's the possibility that they might want to change to a different brand of insulator or something like that in the future, which would be assigned a new part number when it came into our warehouse.
I would definitely advise against trying to treat a unit as a part comprised of other parts. You can use a query to sum the unit's parts with other parts of the order, it's just going to be tricky, like Stewart says.
This is the so-called classic Bill of Materials problem.
Check http://en.wikipedia.org/wiki/Bill_of_materials for the general description.
I would advise to use the selected item to record the "sub items" as a recipe so you always have a reference to the "construction" at the moment of ordering. Just in case someone changes the construction of the item after ordering.
Nic;o)
My warehouse stocks 3 items, Insulators, Poles, and Crossarms. ALL orders are for units. But a unit is composed of items, from 1 to many. The tables are: - Table1=Orders
-
Order# Unit Qty Comment
-
1 14 2 Pole Assembly
-
1 21 14 Crossarm
-
2 21 1 Crossarm
-
Table2=items in units
-
Unit# Item# Qty
-
14 6187 1
-
14 1442 2
-
14 1741 6
-
21 1442 1
-
37 6187 1
-
72 1442 1
-
Table3=item list
-
Item# Desc
-
1442 Crossarm
-
1741 Insulator (Glass)
-
6187 Pole 60'
Warehouse gets: - SELECT Table1.[Order#], Table2.[Item#], [Table1].[Qty]*[Table2].[Qty] AS Quantity, Table3.Desc
-
FROM (Table1 INNER JOIN Table2 ON Table1.[Unit#] = Table2.[Unit#]) INNER JOIN Table3 ON Table2.[Item#] = Table3.[Item#]
-
ORDER BY Table1.[Order#];
Actually, [tbl_units_ordered] should not be linked neither to [Units] nor to [Items] if particular item model/supplier/price is expected to change in time.
My point here was only that by ordering in Units, not Items, for everything, the tables & queries were quite practical. I didn't try to collect rows of the query into total lines, apply prices, or consider how the distribution system works.
By creating a Units table, and considering everything as units, the problem became fairly simple. Actual implimentation would need to track costs, shipping schedules, urgency (restore electricty after storm, etc.) For those Units comprised of a single Item, these could be added to the Units table using UNION, whenever needed.
I just disagreed with:
I would definitely advise against trying to treat a unit as a part comprised of other parts
your requirement is a fairly tricky one to implement in a fully-general way.
Looks perfect in a case if unit items collection will not be modified once defined
@Stewart Ross Inverness
I was actually talking about a part comprised of other parts, and only parts. Having everything in units comprised of parts is a great idea, and quite different.
NeoPa 32,556
Expert Mod 16PB
Hi April.
I would say the first thing to consider is clarifying in your head, before you go any further, exactly how you'd like this to work. - Does it want to be entered and stored as a composite item rather than as a list of parts?
- Does the operator want to enter a part OR an item from the same control? Or would you want a separate process for entering the composite items?
With something which is obviously more complicated than was anticipated, it helps enormously to tie these issues down before proceeding more deeply into the issue. It enables you to ignore many branches of your decision tree.
Good luck anyway.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Philipp Lenssen |
last post by:
I have a site that runs in strict mode in IE6, and IE is making <td>s
for simple two-digit numbers really wide. So I want to restrict it (and
right-align it) by using <td class="number"> and...
|
by: Reza Nabi |
last post by:
Dear All:
Banckgroud: I have a datagrid which lives inside a repeater. Which is
working fine. What i need is to dyanamically set the column width of the
grid (which lieves inside the repeater)....
|
by: Hadley Willan |
last post by:
Hi,
I was wondering if it's possible to order the result set by some of
the set contained in an IN clause.
For example.
SELECT * FROM v_fol_unit_pub_utmpt WHERE folder_folder_object = 100120...
|
by: Sigmathaar |
last post by:
Hi, I'm need some advice about lists and vectors. I'm doing a program
who needs to have sequential access of a non ordered unit of objects
whose size decreases almost each time the sequence is...
|
by: Brie_Manakul |
last post by:
I need to set up an if else to show different weather scripts based on
the city selection they choose. Any help on this would be great.
Thanks!
<%@ page language="java" import="java.util.*,...
|
by: balasam |
last post by:
Dear friend,
I am having Linux platform.I am declaring the array of
structure in one file and how can access the values of the array of
structure in another file.
Using " extern " ,to extern...
|
by: Lance |
last post by:
Hi all,
Last week a reply from Tom Shelton helped me with declaring a Structure containing an
array of a priviously defined structure like so :
/////
Public Structure GM_ProjAttrValue_t...
|
by: yawnmoth |
last post by:
I have two tables on the following page:
http://www.frostjedi.com/terra/scripts/demo/tables.html
I'm trying to make the top one look like the bottom in using only
CSS. In FireFox, what I...
|
by: Alvin SIU |
last post by:
Hi all,
I have 6 tables inside a MS Access 2003 mdb file.
I want to convert them as DB2 version -8 tables in AIX 5.2.
I have exported them as 6 XML files.
The XML files look fine.
Each...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |