Results 1 to 6 of 6
  1. #1
    Michael1926 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2018
    Posts
    10

    Type in a querry and return results how?

    Hello everyone!

    I've a question about querries in access. So, how can i type for a example the names of the companies and in return to show me, whether the products are new or not and simultaneously the name of that product. The only way i knew until now was the fact that we were checking the boxes with the specific name fields and then in criteria used the 'greater than ,or ,and ,name etc'

    Thank you in advanced for the answers!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You've said nothing about your tables or their fields, nor did you provide any information about the query. What you ought to have is a table for "companies" (suppliers? customers?), one for products, and assuming any given product could be related to more than one "company", a junction table that accommodates such a many to many relationship. Then you could enter any given company name into the proper field from that table and get a set of records that shows which products are related to that company. You might think that only the companies and junction table (tblCoProds?) would be required, but the unique product id (often an autonumber) from tblProducts is what ought to be in the junction table as a foreign key. That means you'd be looking at a number rather than a product id, which wouldn't help much.

    If that is not your setup, or if not much of that makes sense, you should step back at research db normalization as well as query design based on good normalization.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Michael1926 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2018
    Posts
    10
    Hello thank you for the answer and sorry for not being so cleared about my question. Can someone type a company and in return to show him the results he wants? Not to just select the companies but type the company.

    As far as my data, the query asks me to type a company and in return to show the first and last name, from which country does the product come and the quantity.

    My tables are 3. Sailsman, products, sales. Also this is a many to many relationship right?

  4. #4
    Michael1926 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2018
    Posts
    10
    Also the sheet asks me to create 2 tables woth names, first table sailesman with specifics features and the second table productd with specific fields as well.

    Then to create a third table which it will take from the first table the codesailsman and from the second one the codeproduct with some fields as well.

    Ive put in the first table as a primary key the codesailesman and on the second table the codeproduct , at the third table i didnt put any primary key am i correct?

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can someone type a company and in return to show him the results he wants?
    Yes; it would be called a parameter query. To test its behaviour when it is a query that you run directly, create a new query and for the company name field, enter a question/prompt in square brackets (e.g. [Enter company name]) in the company name criteria field of the query and run it. You should get a prompt dialog for the name. The disadvantages to this method are that you cannot control what is entered, thus the result may be no records.

    Alternatively, it's more reliable to have this query refer to a textbox or combobox field on a form that contains the value. This way, you can control what is fed to the query if you validate the chosen data first. For your case, to accept user typing requires this to be a textbox to keep it simple, otherwise more complex handling would be required.

    As for your other questions, I have no idea what your business model is, nor how you've structured and related the tables. From your minimal description, you may have a one to many to many relationship - i.e. one salesman record per salesman table, but each salesman can sell many products many times. Suggest you either:
    - concentrate for now on your original problem (a way and a place to type in a company name for your query results) or
    - research db normalization if you have design concerns around what one to one vs one to many relationships are.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to micron's questions and comments, is this a homework assignment?

    Some thoughts to consider:

    -if you allow user(s) to type directly into a form control, there is a high probability of spelling mistakes.
    -prevention of typos is often done by validating the input and then having user(s) select names from a controlbox(dropdown)

    -every table in a relational database should have a primary key

    We know nothing of your set up or business--so more info and details would be helpful.
    If you have some sample data that you could post, that would be useful.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to get a query to return results for null/0 value?
    By NikoTheBowHunter in forum Queries
    Replies: 8
    Last Post: 08-28-2018, 09:04 AM
  2. Criteria to return results from last 30 days
    By Guitarzan in forum Queries
    Replies: 3
    Last Post: 07-11-2017, 11:04 AM
  3. Replies: 6
    Last Post: 04-04-2017, 02:50 AM
  4. Replies: 6
    Last Post: 12-15-2014, 07:53 PM
  5. Replies: 7
    Last Post: 08-13-2010, 02:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums