pulling data from a union query into a form or report, to calculate values

[NigelW]

Hi all,


Sorry for this, however I seem to be chasing my own tail trying to solve my issue.
I am creating a database system, which among other things will include a simple stock / inventory function.
Among other tables I have 3 parts tables:

Raw materials
Fittings / assemblies
Manufactured Items

Each has different fields, but each has two particular fields in common
PartNumber
ItemValue

I have a Union Query "AllPartsQuery", to pull all partNumber's and ItemValues into a single "List" (Query)
This is used to generate the combo box used to select the PartNumber entry in the Stock Table PartNumber Field.
Among other fields the Stock Table has the current STOCKQTY.

At a future date, say stock taking, I will wish to run a report / query / form as appropriate to show the value of each line item, and of course a total stock value.

I seem unable to get a method to make this work, I don't want (if avoidable, which is what I am trying to do) to put a Item value in the STOCK Table, I want to pull it from the appropriate Parts table or the "AllPartsQuery".

I have tried making relationships, using a joining table, I have tried using the "AllPartsQuery" to utilise the ItemValue, but I seem to keep failing, one thing or another doesnt work.

What should be the "normal / standard" way of doing this?
I don't really want to have ALL product types (Raw materials / fittings / manufactured items) in the same table, as there is very little shared between these items,
other than they all have a unique part number and each has a unit value.

Hope this isn't too daft a requirement, but I am not getting anywhere.

Many thanks in advance for any assistance, or pointing to an appropriate site / video.

Regards
Nigel

[mike60smart]

Hi Nigel

Can you upload a zipped copy of the database?

If not then a screen shot of the Relationships window so we can see the table structures/

[NigelW]

screen shot, with some indications as to intent. I can zip and upload, but at this moment things are not entirely all there, as I have been mucking about.

[Gicu]

So what happens if you use the union query and the stock table to get your query/report? Are the part numbers unique among the three tables?

Cheers,

[NigelW]

Hi,
Yes the partnumbers are Unique in each and between all tables.
Your answer triggered a thought, of using the union query as part of another query, to use in the form or report.
I created a link, in the Query Design between the Stock and the AllPartsQuery, this then worked, and permitted me to then pull the data together, the unitvalues, and the stockqty.
I then have entry to calculate the field "LineValue" in the new query. So far so good.
I am now struggling to on the form, to provide a total stock value. The "LineValue" is on a subform, whilst the "TotalStock" is on the mainform.




Googling, this seems to be a not uncommon issue, however the answers seem variable, and to be honest half of them I didn't quite grasp.
Is it possible to Sum the values generated as a calculated field in a Query, on a form or report?
I.e. Query calculates
LineValue: [STOCK QTY]*[UnitValue]
This is then displayed on Form as "LineValue"
Can I then sum the "LineValue"'s into a Total Value?

Many thanks for the help and Patience.

Regards
Nigel

[NigelW]

Hi,
Yes the partnumbers are Unique in each and between all tables.
Your answer triggered a thought, of using the union query as part of another query, to use in the form or report.
I created a link, in the Query Design between the Stock and the AllPartsQuery, this then worked, and permitted me to then pull the data together, the unitvalues, and the stockqty.
I then have entry to calculate the field "LineValue" in the new query. So far so good.
I am now struggling to on the form, to provide a total stock value. The "LineValue" is on a subform, whilst the "TotalStock" is on the mainform.




Googling, this seems to be a not uncommon issue, however the answers seem variable, and to be honest half of them I didn't quite grasp.
Is it possible to Sum the values generated as a calculated field in a Query, on a form or report?
I.e. Query calculates
LineValue: [STOCK QTY]*[UnitValue]
This is then displayed on Form as "LineValue"
Can I then sum the "LineValue"'s into a Total Value?

Further update I have managed to "Fix" it. I needed to create the sum functioned total ON the subform
(not the main form) and then simply make the control source of the mainform box the subform calculated value box

Many thanks for the help and Patience.


Regards
Nigel

[Gicu]

Glad to hear you solved it! Good luck with your projects!
Cheers,