Trying to Using a Query and a Dlookup function

[Guest]

Hi,
I’m trying to get a query to work using a Dlookup function. I’m not sure if
what I’m doing will work in my case. Maybe you can help me?
I have a table called (tblgrandtot) that has 4 fields/columns called (ID),
(Total), (option) and (invoice#). The (option) column/field has 5 options
1,2,3,4,5,. each option has a total which is the (total) column/field. The
(ID) column/field is auto numbered which is the primary key, and the
(invoice#) is a foreign key to the PrimaryBid_Master table which also has a
field/column called invoice#.
I need to make a query that will allow me to select the (Total) field/column
where the (Option) field/column = 1.

I made a query in design view called (tblgrandtotQ) based on the
(tblgrandtot) and added the fields/columns (total), (option).
I'm not sure what to add to the criteria section of the query so it will
select and show the (total) of (option) 1.

I also added a dlookup function to a text box on my form called (labor). In
the control source, that looks like this:
=DLookUp("[total]","tblgrandtotQ")
I'm confused on what will trigger/activate this dlookup function so it will
retrieve the needed data? To be truthfull I don't know if any of this is
correct? But it is the best I could do with the presented examples, I found
in the help files.

I'm rookie at queries, but I do believe they are used to extract data that
is needed through out the database. So it appears to be a very important part
of making databases.

Thanks--Any help/suggestions will be greatly appreciated.

 

[Jeff Boyce]

Are you saying you want your query to total the field you've named [Total],
where [Option] = 1?

Take a look at a Totals query (the Greek capital sigma - backwards '3'),
using Sum for the [Total] field and Where for the [Option] field, and '1'
for the criterion for the [Option] field.

If you've set the ControlSource of a control on your form to what the
DLookup() function returns, you'll probably want to use Access HELP to make
sure just what it is returning.

 

[Guest]

Hi, Jeff,
No, thats not what I was saying, sorry about the miscommunication.
The field named [Total] is already totaled, I just need the value in this
field where the [option] field = 1.

Thanks--I appreciate your help, very much.

Are you saying you want your query to total the field you've named [Total],
where [Option] = 1?

Take a look at a Totals query (the Greek capital sigma - backwards '3'),
using Sum for the [Total] field and Where for the [Option] field, and '1'
for the criterion for the [Option] field.

If you've set the ControlSource of a control on your form to what the
DLookup() function returns, you'll probably want to use Access HELP to make
sure just what it is returning.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi,
I’m trying to get a query to work using a Dlookup function. I’m not sure if
what I’m doing will work in my case. Maybe you can help me?
I have a table called (tblgrandtot) that has 4 fields/columns called (ID),
(Total), (option) and (invoice#). The (option) column/field has 5 options
1,2,3,4,5,. each option has a total which is the (total) column/field. The
(ID) column/field is auto numbered which is the primary key, and the
(invoice#) is a foreign key to the PrimaryBid_Master table which also has a
field/column called invoice#.
I need to make a query that will allow me to select the (Total) field/column
where the (Option) field/column = 1.

I made a query in design view called (tblgrandtotQ) based on the
(tblgrandtot) and added the fields/columns (total), (option).
I'm not sure what to add to the criteria section of the query so it will
select and show the (total) of (option) 1.

I also added a dlookup function to a text box on my form called (labor). In
the control source, that looks like this:
=DLookUp("[total]","tblgrandtotQ")
I'm confused on what will trigger/activate this dlookup function so it will
retrieve the needed data? To be truthfull I don't know if any of this is
correct? But it is the best I could do with the presented examples, I found
in the help files.

I'm rookie at queries, but I do believe they are used to extract data that
is needed through out the database. So it appears to be a very important part
of making databases.

Thanks--Any help/suggestions will be greatly appreciated.

 

[Jeff Boyce]

?You have a table that only holds "totals"? Where do they come from?

You can use the "Totals" query I mentioned to sum up detail records, rather
than creating a new table to hold the totals.

In your existing table, your query simply needs to indicate the criterion
(?1?) for the [Option] field, and to display the [Total] field.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi, Jeff,
No, thats not what I was saying, sorry about the miscommunication.
The field named [Total] is already totaled, I just need the value in this
field where the [option] field = 1.

Thanks--I appreciate your help, very much.

Are you saying you want your query to total the field you've named [Total],
where [Option] = 1?

Take a look at a Totals query (the Greek capital sigma - backwards '3'),
using Sum for the [Total] field and Where for the [Option] field, and '1'
for the criterion for the [Option] field.

If you've set the ControlSource of a control on your form to what the
DLookup() function returns, you'll probably want to use Access HELP to make
sure just what it is returning.

--
Regards

Jeff Boyce
<Office/Access MVP>

Hi,
I’m trying to get a query to work using a Dlookup function. I’m not

sure
if

what I’m doing will work in my case. Maybe you can help me?
I have a table called (tblgrandtot) that has 4 fields/columns called (ID),
(Total), (option) and (invoice#). The (option) column/field has 5 options
1,2,3,4,5,. each option has a total which is the (total) column/field. The
(ID) column/field is auto numbered which is the primary key, and the
(invoice#) is a foreign key to the PrimaryBid_Master table which also

has
a

field/column called invoice#.
I need to make a query that will allow me to select the (Total) field/column
where the (Option) field/column = 1.

I made a query in design view called (tblgrandtotQ) based on the
(tblgrandtot) and added the fields/columns (total), (option).
I'm not sure what to add to the criteria section of the query so it will
select and show the (total) of (option) 1.

I also added a dlookup function to a text box on my form called

(labor).
In

the control source, that looks like this:
=DLookUp("[total]","tblgrandtotQ")
I'm confused on what will trigger/activate this dlookup function so it will
retrieve the needed data? To be truthfull I don't know if any of this is
correct? But it is the best I could do with the presented examples, I found
in the help files.

I'm rookie at queries, but I do believe they are used to extract data that
is needed through out the database. So it appears to be a very

important
part

of making databases.

Thanks--Any help/suggestions will be greatly appreciated.