How to reference a value from a query in a form (1 Viewer)

norburao01 · Aug 14, 2019

[Solved] How to reference a value from a query in a form

Hi All,

I'm making a form to view and update components on a project.
The majority of the information is in one table called 'component list', however a component can include pre-bought parts, these pre bought parts are used a lot of time so I have made another table for these, named 'common products list' this way i can reference the 'common products list' instead of keeping entering the information over and over again.

The issue I'm having is coming when trying to find the overall cost of each component, as I have to do a sum of the component cost itself (from component list table) and of the pre-bought part (from the common products list table) that will be used on that component.

I tried to solve this by making a query, with columns for the component ID, the component and the pre-bought part cost with a sum column. (shown in attachment)
This worked but I don't know how to show this sum on the corresponding form.

Any help for how I can show this, Overall cost value of the component on the corresponding components form, would be greatly appreciated.

Apologies, know I might not have used all the correct terms I'm still quite new to using access.
Thanks

Uncle Gizmo · Aug 14, 2019

The image doesn't tell me anything?

Ranman256 · Aug 14, 2019

Q1 is the query you have shown
Q2, use to sum the values in Q1

jdraw · Aug 14, 2019

There is a long thread here that may be helpful. There is a discussion and advice regarding parts and assemblies of parts. May be a good reference to assist with concepts.
A good description of your business without jargon and database terms would be useful to readers for understanding you, your environment and requirement.
Good luck.

norburao01 · Aug 15, 2019

@jdraw, thanks I have looked through the thread you suggested and thank you for the advice. However the field connections and relationships is not what I am struggling with.

I think I over complicated the question, what I need help with is referencing a particular cell in a query (known as overall cost) to a form, identifying which two go together with a unique identifier (Known as Component ID).

So my form displays information about a component (identified by a unique identifier (Known as Component ID)).
The query does some sums about each component price (again identified by Component ID).
I want to show the total cost of each component that I worked out with the query, in the form, for each of the components (identified by the Component ID)

I hope this is more clear but please tell me if its not and I can go into more detail

arnelgp · Aug 15, 2019

create a New Query from yourTable and your query of the over all costs.
use Left join:

Code:

select yourTable.*, qryOverAllCost.[Overall Cost] from yourTable Left Join qryOverAllCost On yourTable.[Component ID] = qryOverAllCost.[Component ID]

use this new query as the recordsource of your form.

norburao01 · Aug 15, 2019

@arnelgp
I do apologise but which table do you mean by 'yourTable'? again I'm still quite new to access and am a bit confused
Thanks for the quick reply though

arnelgp · Aug 15, 2019

the component table name you are updating.

norburao01 · Aug 15, 2019

@arnelgp I'm having trouble getting the SQL code for the new query to work, I cant get it to reference the query where I have the overall cost.

CODE:
SELECT [Compenet List].[Component ID], qry.[Component cost logic].[Overall cost]
FROM [Component List]
LEFT JOIN qry.[Component cost logic] ON [Component List].[Componet ID] = qry.[Compoent cost logic].[Component ID]
ORDER BY [Component List].[Component ID];

note: 'component cost logic' is the query where I have the overall cost
I have tried without the preface qry and with qry, and as shown qry.

arnelgp · Aug 15, 2019

remove the qry. from your query.
also check each spelling of "Component cost logic", there is one that is misspelled.

norburao01 · Aug 15, 2019

@arnelgp
Thanks I have done those changes but I now get an error stating:
'The specific field '[Component List].[Component ID]' could refer to more than one table listed in the From clause of your SQL statement'

I don't fully know what this means but I only have one Component ID column in the Component list table.

Can you advise?

arnelgp · Aug 15, 2019

better use the Design view.
create New Query by clicking on the Ribbon.
Create->Query Design.
Select Component List table.
on the Query tab, select Component Cost Logic query.
drag [Component ID] of component list table to the [Component ID] of [Component Cost Logic] query.
right click on the joining line.
on the Join property, select Option 2.
from component list table, double click component id.
from component cost logic, double click overall cost.
run and save your query.

norburao01 · Aug 15, 2019

Ok so this gets me a query with just the Component ID and the Overall cost

But I have the same problem as before, how do I show this Overall cost in the components form?

arnelgp · Aug 15, 2019

you add other fields from component list table to the query that you want to show in the form.
use the query you made as the RecordSource of your form.
drag the [component cost logic] field to your form.

jdraw · Aug 15, 2019

Perhaps you should post a copy of your database in zip format. Include instructions to highlight the specific issue and sample of what you want as output. Sample can be a simple expression identifying components, component cost, overall cost or whatever is sufficient to "describe/display" expected output.

Also, as arnelgp said if you want the info to display on the form, the info should be in the record source of the form.

norburao01 · Aug 15, 2019

Thank you for the help @arnelgp and jdraw.

I will post a copy of the database so its easier for you to see how I would like the format of the form.

On the 'Component in detail review' (the form) I would like it to show the overall cost as a value (box in there its just unassigned at the moment). Another important point is that I want to edit the values in the form.

If you think remaking the Form using Query previously mentioned as the record source. Adding the data from the Components list will work the best I will do this. (@arnelgp's method). But if there is better way you can think of that will work for this format I will do that.
I have tried remaking the form using the query but I am unable to edit the values shown so would need advice on how to overcome this.
Also I would not be editing the overall cost, I would be editing the two sub costs (Additional costs and Price (from the two tables Component list and Common products list respectively)

arnelgp · Aug 15, 2019

here take a look at the new query, see the recordsource of the form in design view.
the the overall cost on the form.

norburao01 · Aug 15, 2019

@arnelgp I've had a look at the form and I cannot edit or add new data.
I need to be able to edit from the form, is this because its recordsource is from a query?

arnelgp · Aug 15, 2019

here I change it to dlookup.
it's updateable now.

norburao01 · Aug 15, 2019

Thank you @arnelgp you have been very patient and helpful.
Thats everything sorted

How to reference a value from a query in a form (1 Viewer)

Registered User.

Attachments

Nifty Access Guy

Well-known member

Super Moderator

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Super Moderator

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Similar threads

Users who are viewing this thread