Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter Query (Access 2003)

    I have a database with two tables. Both tables have the same fields with data in Field 1 & 2 the same. One table is experimental data, the other table is standard data. I created a simple relationship between the two tables on Field 1.

    I created a query where a parameter is entered for Field 1 and another parameter for Field 2 in the Experimental table. I want the Form created from the query to display the Experimental data at the top and the Standard data on the bottom. Currently I have asked for four parameters in the query -- Field 1 & 2 from the Experimental and Fields 1 & 2 in the Standard. This works.

    Is there a way to enter the Field 1 and Field 2 parameter just once, since the fields in the two tables have the same Field 1 and Field 2 data?

    I don't know how to code, so if there is a way within Access routines, I'd appreciate it.

    Thanks,
    Craig.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query (Access 2003)

    It should be possible to do this entirely within the Access interface, without using code, but we'd need to know more about the setup of your database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query (Access 2003)

    Hans:

    Thanks for your willingness to assist.

    Attached is a copy of the database. I deleted any relationships so I could delete and modify some of the data. I wouldn't be surprised if the relationships were the problem.

    What I want to do is enter, for example, 123-V-4567 for the FIELD1 and 066-5221 for FIELD2 and have that data from both tables appear in the Form.

    Thanks again,
    Craig.
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameter Query (Access 2003)

    Hi Craig

    There is a way to do this but it will require you to clean up the structure of the table. Both tables are the same accept that C of A requires another field to match Standard Data. Ensure that the Fielf properties for each of the fields in each of the two tables is exactly the same. It appears that one is created in Access and has Field properties and masks where the other is an import from an external file. Once these are the same you could use a Union query like so:

    SELECT *

    from [C of A Query]

    UNION

    SELECT *
    from [standard data]
    ;

    You can then base the form on this query

    HTH
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query (Access 2003)

    I only get prompted once for each field, both when opening the query and when opening the form, so perhaps the database is not representative of the real one? I don't think a relationship between the tables matters here.

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter Query (Access 2003)

    What I want to happen is enter Field1 data once and Field2 data once and have the query show me the data from both tables. I know I can accomplish this by setting parameters for Fields1 & 2 for both tables (total of 4 parameters), but since the parameter data is the same for both tables I'd like to be able to enter once.

    I'll also clean up the table data as suggested by Jerry -- thans, Jerry.

    Craig.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameter Query (Access 2003)

    If you join the tables on both Field1 and Field2, the parameters you enter for Field1 and Field2 in one table will also hold for the other table.

    If you want the records from the two tables to appear one below the other instead of next to each other, you'll have to use a union query, as suggested by Jezza.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •