Order Query needed to dissolve Unit order into components

[KatBaroo]

Hi All



please bear with me as I am trying to explain my problem with the query - I have nil experience with sql but can modify basic ones to suit my needs)

I am working on an Order database and have tables for the individual items and prices (they are linked to excel files)

In addition there are units (sub units but not relevant here) that are made up of different items. (tbl for units + subunits, subunits + items that make up the units)

Because the customer can order individual items as well as units, I have created 2 tables for the order: 1 x items + 1 x subunits.

I am now trying to dissolve the order for the subunits into the individual items so I am trying to run a query with the tbl for the order of the subunit (tblQuoteDSubUnits) + the breakdown of the subunits (tblUnitsSubComponents). Both have the field SubUnit Code in common.

When I run the query with relationship I get nothing. If I remove the relationship, I get a rows of rows with the one Subunit I am using as sample and different items against it. I know I have to somehow tell the query to list only the items of that unit but am hitting a mental block at the moment.

Hope this was clear as mud.

Thank you for any attempt at helping.

KatBaroo
(because the Yak yaks so nicely)

[June7]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

[orange]

See this post re units/assemblies and review the thread dialog for related information and concepts.

[KatBaroo]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

Thank you, I will do that.

[KatBaroo]

Thank you. I am still reading the thread and trying to translate it to how it might apply to my database.

[KatBaroo]

Thank you. I am still reading the thread and trying to translate it to how it might apply to my database.

[KatBaroo]

Hi All

thanks for the replies. I have a feeling I wasn't very clear.

For anyone wanting to look at it, I will attach the database - excluding everything not relevant to this question. And sorry I had to zip it as I just couldn't get it to the 500kbOrderbook - Copy.zip

The tables "tblBaseItemAll" and "tblBaseItemPrices" are linked to Excel files as they have too many calculations and variables that need changing for me to include them in access but I have converted them to local tables in the attachment. I am working on this so someone not familiar with this customer can fairly easily take over - always keeping in mind that none of the Sales people are computer experts, sometimes barely computer literate.

I am working currently with 699 items that lead into 63 Units which have in total 266 Sub Units

The query in question is "qryQuoteSubUnitItems". I am trying to list the components used for the subunit quoted in tbl "tblQuoteDSubUnits"

if I run the query with relationships I get no result. If I remove the relationship I get a whole list of nonsense. If I change the relationship type it still doesn't make sense.

I have a feeling there is a mismatch somewhere but I cannot find.

To be honest, I probably should not have started this project - I have worked with Access last about 20 years ago and it seems forgot most of it unfortunately and am now in over my head.

Regards

KatBaroo

[alansidman]

After looking at your tables, I see that you are doing lookups in your tables. This is a developers NO NO. Look ups should be done in forms. I don't see any forms in your sample file but you did state that the data originates in Excel.

Read here----->http://access.mvps.org/access/lookupfields.htm

[KatBaroo]

Thank you. I have meanwhile removed the lookups from the tables - the memories are returning. I have also converted one of the linked tables to a local table and am working on the pricing one.

I removed all forms and any other extras to make the database as small as possible and still didn't manage to get to the 500kb.

[Micron]

Just zip it as you did before. Anyone who helps out here is fine with dealing with posted .zip files. I use 7zip exclusively while others don't have it so stick to zip files for a broader audience.

[KatBaroo]

I will do that. Once I have made changes to the database as far as I got to apply the tips I have gotten so far.

[ArviLaanemets]

I'd advice a table for all items (both purchased and produced, items produced as components for other items included), like:
tblItems: ItemID, ItemType, ItemName, ..., ItemStatus (ItemType as a numeric value defining the item as e.g. purchased or produced or ..., ItemStatus as numelic value defining item as e.g. normal or obsolete or ...)

A table for price history, like:
tblItemPrices: ItemPriceID, ItemID, PriceFromDate, PurchasePrice, SalesPrice;

A BOM list for any produced items, like:
tblBOM: BomID, ProductID, ItemID, ComponentID, BomRow, ComponentQty, ... (where the structure and quantities of needed components for one product unit of every produced item in tblItems is described)
ProductID is for product described, ItemID is for Item produced at this step of BOM list, and ComponentID is for items used to produce ItemID at this step of BOM list. This table allows easily to calculate quantities of all components/materials and how much all this will cost (for single product unit, or for ordered amount;

When you need to calculate total pricing, then you also need additional table where all production operations and the cost of them (hardware costs + personal costs) are described, like:
tblProdOperations: ProdOpID, ProductID, BomID, [Workstation], Operation, OperationCost
(You can split OperationCost to different cost components). For every BomRow for product, the table must have at least one operation row. It is impossible to be more specific about this table with info currently present.

And you need a procedure, which will update SalesPrice in tblItemPrices for produced items (accounting for component costs, operations costs, and any other price components like margins, VAT, etc.) either on request, or on schedule.

With all this present, your query will be a simple one.

[KatBaroo]

Hi Avril

thank you so much for taking the time to reply. I have most of the tables.

The item price is already a calculated value so I am using the tblBaseItemAll (has the purchasing price in US$) and tblPricingExtraCost (which has percentages, added cost) as well as tblExchangeRateLatest (which is updated via query) to calculate the sales price.

Since posting here I have started to rework some of the tables and queries as I got great tips already. If I get stuck again, I will start another post.

[KatBaroo]

Thanks everyone. I have marked the thread as solved for now as I am reworking the database. I rather post again if I hit a snag.