Trying to Using a Query and a Dlookup function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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.

Jeff Boyce said:
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>

patentinv said:
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.
Click to expand...
Click to expand...
 
?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>

patentinv said:
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.

Jeff Boyce said:
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>

patentinv said:
Hi,
I’m trying to get a query to work using a Dlookup function. I’m not
Click to expand...
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
Click to expand...
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
Click to expand...
(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
Click to expand...
important
part
of making databases.

Thanks--Any help/suggestions will be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top