Welcome to SPN

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

Sign Up Now!

Parameter queries

Discussion in 'Information Technology' started by Pete Rothery, Jul 28, 2006.

  1. Pete Rothery

    Pete Rothery
    Expand Collapse
    Guest

    I have several users and use parameter queries to returm data specific to
    that user. Is there any way they can "sign in" once and just get the data
    specific to them??
     
  2. Loading...

    Similar Threads Forum Date
    USA New documents reveal parameters of NSA’s secret surveillance programs Breaking News Jun 21, 2013
    Sikh News Haryana overtakes Punjab on most eco parameters (Express India) Breaking News Oct 1, 2007
    Sikh News Haryana overtakes Punjab on most eco parameters (Indian Express via Yahoo! India News Breaking News Oct 1, 2007
    Importance of Hukamnama - Some Queries Sikh Sikhi Sikhism Jun 14, 2009
    Queries History of Sikhism Oct 26, 2006

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    yes

    store who they are in a "defaults" table

    you can just use one record if you like and change that...

    assuming you are collecting this data on a form... after
    they sign in

    dim strSQL as string
    strSQL = "UPDATE DefaultTablename " _
    & " SET NameUser = '" _
    & me.NameUser & "'"

    then, for the criteria in your query...

    criteria --> nz(dFirst(
    "NameUser",
    "DefaultTablename"),"")




    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Pete Rothery wrote:
    > I have several users and use parameter queries to returm data specific to
    > that user. Is there any way they can "sign in" once and just get the data
    > specific to them??
     
  4. Pete Rothery

    Pete Rothery
    Expand Collapse
    Guest

    Thanks a lot for the advice. I not so hot with SQL but idea of names in a
    little table was enough to get me thinking right so could do it with design
    view stuff.
    How do you get that good???

    Luv
    Pete




    "strive4peace" <"strive4peace2006 at yaho" wrote:

    > yes
    >
    > store who they are in a "defaults" table
    >
    > you can just use one record if you like and change that...
    >
    > assuming you are collecting this data on a form... after
    > they sign in
    >
    > dim strSQL as string
    > strSQL = "UPDATE DefaultTablename " _
    > & " SET NameUser = '" _
    > & me.NameUser & "'"
    >
    > then, for the criteria in your query...
    >
    > criteria --> nz(dFirst(
    > "NameUser",
    > "DefaultTablename"),"")
    >
    >
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > Pete Rothery wrote:
    > > I have several users and use parameter queries to returm data specific to
    > > that user. Is there any way they can "sign in" once and just get the data
    > > specific to them??

    >
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Parameter queries -- basic SQL syntax

    Thanks, Pete :) you are welcome

    Don't let the SQL acronym intimidate you, it is not tough to
    learn the basics.

    ~~~~~~~~~~~ SQL background ~~~~~~~~~~~

    SQL is Structured Query Language

    It is a statement to get data out of one or more
    tables/queries. This is what Access stores for:

    1. Queries (just shows the QBE grid for convenience --
    choose View, SQL)

    2. RowSource for Comboboxes and Listboxes (if list does not
    come directly from a table since a query is actually an SQL
    statement)

    3. RecordSource for Reports and Forms (if list does not come
    directly from a table)

    Queries can be different types, for instance:
    1. Select
    2. Crosstab
    3. Make-Table
    4. Update
    5. Append
    6. Delete

    Select and Crosstab queries show data while the others are
    "action" queries" and do not display data -- so you cannot
    use them for record sources or for row sources.

    To expand and reiterate:

    Queries are a very powerful tool in Access -- you can do far
    more than simply use them to select data

    .... Queries can be ACTION Queries...they DO things, not show
    you things

    they can add data to a table --> APPEND Query

    they can make changes to a table --> UPDATE Query

    they can delete records from a table --> Delete Query

    they can make tables --> Make-Table Query

    they can transpose data --> Crosstab Query (which is also,
    actually, SELECTing data from a table since there is not action)

    Internally, Access stores an SQL (Structured Query Language)
    statement for queries*, not the visual representation you
    see when you design queries -- the QBE (Query-By-Example)
    grid is for your benefit, so you can use drag-n-drop and
    pick methods and visually look at things better.

    *this is why it is so important to get comfortable with
    lokking at SQL statements

    A great way to become more familar with SQL is to LOOK at
    the SQL everytime you make a query. Until you actually start
    to look, you never realize how easy and sensible it actually
    is... and It really helps to use Aliases (short
    abbreviations) for tablenames as it makes the SQL statement
    shorter.

    ~~~~~~~~~~~ SELECT ~~~~~~~~~~~

    BASIC SQL SYNTAX

    SELECT fieldlist
    FROM tablename
    IN anotherdatabase.mdb
    WHERE conditions
    GROUP BY fieldlist
    HAVING conditions for fields that are grouped
    ORDER BY fieldlist;

    ~~~~~~~~~~~ APPEND ~~~~~~~~~~~

    An Append Query is a select query preceeded by

    INSERT INTO Tablename (field1, field2, etc )

    ~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
    An Update Query first identifies the tables that are used

    UPDATE table1 INNER JOIN table2 ON table1.keyfield =
    table2.keyfield

    Then identifies what to change

    SET table1.fieldtochange = expression

    Then, if you have criteria...

    WHERE table.strField = 'somevalue'
    AND table.numField = 99
    AND table.dateField = #1/1/06#

    ~~~~~~~~ MAKE TABLE ~~~~~~~~

    SELECT fieldlist
    INTO tablename
    IN c:\path\anotherdatabase.mdb
    FROM tablename
    WHERE conditions to process before recordset is made
    GROUP BY fieldlist
    HAVING conditions for fields that are grouped
    ORDER BY fieldlist;

    ~~~~~~~~~~~ DELETE ~~~~~~~~~~~

    DELETE A.*
    FROM tblArticles AS A
    WHERE (A.somefield=2);

    ~~~~~~~~~~~ CROSSTAB ~~~~~~~~~~~

    TRANSFORM Count(B.Fieldname1) AS FieldAlias
    SELECT
    A.Fieldname2,
    A.Fieldname3
    FROM Table2 AS B
    INNER JOIN Table1 AS A
    ON B.someID = A.someID
    GROUP BY
    A.Fieldname2,
    A.Fieldname3
    PIVOT By B.Fieldname1;

    you can use equations to pivot (this will be column
    headings). For instance, if you want the column headings to
    be year and month, you can do this:

    PIVOT Format([DateField],'yy-mm');

    If you also want a column that totals whatever you have in
    all the VALUE columns (the expression after TRANSFORM),
    repeat your transform expression after the SELECT keyword
    and the GROUPBY keywords

    for instance:

    TRANSFORM Count(B.Fieldname1) AS FieldAlias
    SELECT
    A.Fieldname2,
    A.Fieldname,
    Count(B.Fieldname1) AS FieldAlias2
    FROM Table2 AS B
    INNER JOIN Table1 AS A
    ON B.someID = A.someID
    GROUP BY
    A.Fieldname2,
    A.Fieldname3,
    Count(B.Fieldname1)
    PIVOT By B.Fieldname1;

    ~~~~~~~~ ALIASES ~~~~~~~~

    Using Aliases for tablenames (/querynames) makes the SQL
    easier to read.

    For calculated fields, it is best to assign your own field
    alias instead of letting Access use "expr1", "expr2", etc.
    Calculated fields MUST have aliases.

    an Alias follows the keyword AS

    The SQL statement can be selected, copied, then pasted into
    Word for formatting and printing (makes great wallpaper for
    your wall, not your computer ;) as you are learning) or into
    Notepad to have as a popup reference while you are working
    into design view, etc.

    ~~~~~~~~ JOINs ~~~~~~~~

    When you are getting information from more than one table,
    the default join type is INNER JOIN. This means that only
    records in common will be displayed. For instance, if you
    have a table of Companies and a table of Jobs and not every
    Company has done a job, but you want to see ALL the
    companies anyway, you would use a Left Join or a Right Join
    for the type of relationship between tables.

    FROM Companies AS c
    LEFT JOIN Jobs AS J ON c.CompID = J.CompID

    The join is specified as a LEFT JOIN because ...
    ON c.CompID = J.CompID --> C is on the LEFT side of the
    equal sign.

    ~~~~~~~~ PARAMETERS ~~~~~~~~

    If you specify data type in Query, Parameters, the SQL
    statement is preceeded by (for example):

    PARAMETERS [enter category] Text ( 50 );

    while the criteria may be:

    WHERE (B.Category=[enter category])

    ~~~~~~~~ LOOK at SQL ~~~~~~~~

    Whenever you create a query, LOOK at the SQL statement and
    study it for a couple minutes -- it makes sense! Within a
    short period, the confusion will be gone...

    from the menu, choose:
    View, SQL

    First, get comfortable with SELECT statements. Once you
    have them mastered, learn other forms.



    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Pete Rothery wrote:
    > Thanks a lot for the advice. I not so hot with SQL but idea of names in a
    > little table was enough to get me thinking right so could do it with design
    > view stuff.
    > How do you get that good???
    >
    > Luv
    > Pete
    >
    >
    >
    >
    > "strive4peace" <"strive4peace2006 at yaho" wrote:
    >
    >
    >>yes
    >>
    >>store who they are in a "defaults" table
    >>
    >>you can just use one record if you like and change that...
    >>
    >>assuming you are collecting this data on a form... after
    >>they sign in
    >>
    >>dim strSQL as string
    >>strSQL = "UPDATE DefaultTablename " _
    >> & " SET NameUser = '" _
    >> & me.NameUser & "'"
    >>
    >>then, for the criteria in your query...
    >>
    >>criteria --> nz(dFirst(
    >>"NameUser",
    >>"DefaultTablename"),"")
    >>
    >>
    >>
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
    >>
    >>Pete Rothery wrote:
    >>
    >>>I have several users and use parameter queries to returm data specific to
    >>>that user. Is there any way they can "sign in" once and just get the data
    >>>specific to them??

    >>
     
  6. Pete Rothery

    Pete Rothery
    Expand Collapse
    Guest

    Re: Parameter queries -- basic SQL syntax

    Hi Crystal
    Thanks for the SQL lesson - i will give it a go. My db is used at an advice
    agency, it has grown an grown over past 3 years into a bit of a monster, so I
    need to do a bit of streamlining.
    Hope you getting paid for this on a Sunday!
    Thaks again its much appreciated
    Luv
    Pete
     
  7. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Parameter queries -- Using VBA to execute SQL statements

    you're welcome, Pete ;) happy to help

    my payment is your smile :)

    here is a little more to digest...

    ~~~~~~~~ USING VBA to execute SQL statements ~~~~~~~~

    You can execute action queries using VBA

    '~~~~~~~~~~~~~~~~~~~
    dim strSQL as string

    strSQL = "UPDATE tablename " _
    & " SET fieldname = value " _
    & " WHERE conditions;"

    debug.print strSQL
    currentdb.execute strSQL
    '~~~~~~~~~~~~~~~~~~~

    ** debug.print ***

    debug.print strSQL

    --> this prints a copy of the SQL statement to the debug
    window (CTRL-G)

    After you execute your code, open the Debug window
    CTRL-G to Goto the debuG window -- look at the SQL statement

    If the SQL statement has an error

    1. Make a new query (design view)

    2. choose View, SQL from the menu
    (or SQL from the toolbar, first icon)

    3. cut the SQL statement from the debug window
    (select, CTRL-X)

    4. paste into the SQL window of the Query
    (CTRL-V)

    5. run ! from the SQL window
    -- Access will tell you where the problem is in the SQL




    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Pete Rothery wrote:
    > Hi Crystal
    > Thanks for the SQL lesson - i will give it a go. My db is used at an advice
    > agency, it has grown an grown over past 3 years into a bit of a monster, so I
    > need to do a bit of streamlining.
    > Hope you getting paid for this on a Sunday!
    > Thaks again its much appreciated
    > Luv
    > Pete
     

Share This Page