Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2002
    Posts
    23

    Wink Unanswered: Too Many Queries

    Hi all,

    I'm building a db that has about 300 different catagories to choose from. I want to be able to run a query for records by catagory. But I don't want to build 300 queries. How could I get around this?

    Thanks,
    Johnny

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Too Many Queries

    Im not sure what you are trtying to do, but you should be able to run one query with all the fields that you need and order by catagory to see them together.

    Let us know what you are trying to do, maybe we can help a bit more
    Jim

    Originally posted by MountJohnny
    Hi all,

    I'm building a db that has about 300 different catagories to choose from. I want to be able to run a query for records by catagory. But I don't want to build 300 queries. How could I get around this?

    Thanks,
    Johnny

  3. #3
    Join Date
    Oct 2002
    Posts
    23

    Re: Too Many Queries

    Originally posted by JDionne
    Im not sure what you are trtying to do, but you should be able to run one query with all the fields that you need and order by catagory to see them together.

    Let us know what you are trying to do, maybe we can help a bit more
    Jim
    Hi ,

    For instance, I have these 300 catagories (for example, electrical, plumbing, sewage, generators and many more) I have a bunch of tables with things like vendor contact info, procedures, documentation etc. If I want info on one of these 300 catagories, I build a query to search for information by catagory code. For example, electrical would be code 285. I have a button that when clicked, runs the query for anything "285".....but this means I have to build 300 buttons, attached to 300 queries....and I know this is really inefficient. Is there anyway around this (i.e. building a combo box that runs a query based on the selection made?)

    Thanks again,
    John

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    You are so on the right track man. here is what you do.
    create a combo with two colums the first colum will be the code the second will be the decode for that code Ie 285 electrical.
    Create a query with all the joins etc. the the critria section of the catagorie column in the quiey put in like [forms]![form_name]![combo_box]
    make sure that the defualt for the combo box is *
    now on the on click of a button put
    docmd.openquery "query_Name"
    docmd.requery

    and if the form's data set is the query that you created this will run the query and then refresh the form.

    but u were deff on the right path man

    Let us know
    Jim

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    this seems to be exactly what i need but i can't get it working
    the combo box it on form percustomers and is called customers
    query reads as

    SELECT RawData.ACCT_NO, RawData.*
    FROM RawData
    WHERE (((RawData.ACCT_NO)=[form]![PerCustomer]![Customer]));

    but all i get is a box prompting me to enter form!PerCustomer!Customer

    where am i going wrong?
    Attached Thumbnails Attached Thumbnails screen.jpg  
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    First make sure your form is named PerCustomer and your combobox is called Customer. try [forms]![PerCustomer].[Customer]
    tjacobs






    Originally posted by m.timoney
    this seems to be exactly what i need but i can't get it working
    the combo box it on form percustomers and is called customers
    query reads as

    SELECT RawData.ACCT_NO, RawData.*
    FROM RawData
    WHERE (((RawData.ACCT_NO)=[form]![PerCustomer]![Customer]));

    but all i get is a box prompting me to enter form!PerCustomer!Customer

    where am i going wrong?

  7. #7
    Join Date
    Oct 2002
    Posts
    23

    Talking

    Could I do this with a label on a form instead of a combo box? For instance, click on the label and have the query take only the first 4 letters of the label to query by?

    Thanks,
    Johnny

  8. #8
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by MountJohnny
    Could I do this with a label on a form instead of a combo box? For instance, click on the label and have the query take only the first 4 letters of the label to query by?

    Thanks,
    Johnny

    I don't think it will work with a label. Combobox is your best option. I think you are getting the error because instead of forms! you have form. Make that change then go to your form and try again.

  9. #9
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by MountJohnny
    Could I do this with a label on a form instead of a combo box? For instance, click on the label and have the query take only the first 4 letters of the label to query by?

    Thanks,
    Johnny

    type

    [forms]![PerCustomer].[Customer] instead of [form]![PerCustomer]![Customer]

  10. #10
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by tjacobs
    type

    [forms]![PerCustomer].[Customer] instead of [form]![PerCustomer]![Customer]
    The right syntax is
    [forms]![PerCustomer]![Customer]

  11. #11
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by m.timoney
    this seems to be exactly what i need but i can't get it working
    the combo box it on form percustomers and is called customers
    query reads as

    SELECT RawData.ACCT_NO, RawData.*
    FROM RawData
    WHERE (((RawData.ACCT_NO)=[form]![PerCustomer]![Customer]));

    but all i get is a box prompting me to enter form!PerCustomer!Customer

    where am i going wrong?

    Try this

    in your combobox
    RowSource Type : Table/Query
    RowSource: Select RawData.ACCT_NO from RawDAta order by RawData.Acct_no;

    on Click: Event procedure
    docmd.openquery "query_Name"
    docmd.requery


    then in your query
    in the critria section of the ACCT_NO column type [forms]![PerCustomer].[Customer]


    try this

  12. #12
    Join Date
    Oct 2002
    Location
    Greensboro, NC
    Posts
    50
    Originally posted by JDionne
    The right syntax is
    [forms]![PerCustomer]![Customer]
    Yeah but he typed form instead of forms

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    yes it was the missing 's' causing the problem thanks for the help now works perfectly, I HATE BEING DYSLEXIC
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    both are valid as '!' and '.' are interchangeable, before i found this site i'd never seen the '!' notation

    Originally posted by JDionne
    quote:
    --------------------------------------------------------------------------------
    Originally posted by tjacobs
    type

    [forms]![PerCustomer].[Customer] instead of [form]![PerCustomer]![Customer]
    --------------------------------------------------------------------------------

    The right syntax is
    [forms]![PerCustomer]![Customer]
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  15. #15
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Cool neaver knew that I always saw the ! in the help and just assumed,but we all know what assuming does
    Jim

Posting Permissions

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