Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Trying to Using a Query and a Dlookup function

Discussion in 'Information Technology' started by patentinv, Oct 30, 2005.

  1. patentinv

    patentinv
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    Spammers trying to convert me to Islam Questions and Answers Aug 27, 2015
    Leisure Okay, so I am trying to sing in a different language... Business, Lifestyle & Leisure Jul 8, 2014
    Canada Ontario politician trying to lure Quebec doctors affected by values charter Breaking News Sep 16, 2013
    World Why the NATO Powers are Trying to Assassinate Moammar Gaddafi Breaking News Jun 14, 2011
    Malaysia Malaysia Says Extremist Groups Trying to Recruit from Schools Breaking News Jan 7, 2011

  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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" <patentinv@discussions.microsoft.com> wrote in message
    news:5B1077D2-EDA9-4E73-8157-48DE0D64CFC2@microsoft.com...
    > 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.
    >
    >
    >
    >
    >
    >
    >
    >
     
  4. patentinv

    patentinv
    Expand Collapse
    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.

    "Jeff Boyce" wrote:

    > 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" <patentinv@discussions.microsoft.com> wrote in message
    > news:5B1077D2-EDA9-4E73-8157-48DE0D64CFC2@microsoft.com...
    > > 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.
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > >

    >
    >
     
  5. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    ?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" <patentinv@discussions.microsoft.com> wrote in message
    news:39D9974A-C504-4078-867B-F279A25D2182@microsoft.com...
    > 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" wrote:
    >
    > > 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" <patentinv@discussions.microsoft.com> wrote in message
    > > news:5B1077D2-EDA9-4E73-8157-48DE0D64CFC2@microsoft.com...
    > > > 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.
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >

    > >
    > >
     

Share This Page