Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    7

    Unanswered: Need to change a lookup table field

    I have a ms Access database to check special projects for clients. I have imported a table from a paradox database to get the client names. In the table for the special projects the client name field is a lookup table. This works great except for reports. All the reports are sorting on the client ID and not the name. Is there a way to change the field so the names can still be selected by a dropdown but we can sort by name? I tried to redo the lookup table and just select the name field. I still got the client ID numbers.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Your question is a little bit confusing but I'll try my best to answer it with what I think your asking. If your main data table has a ClientID number field, and you have a lookup table which has the ClientID and ClientName, in the query (ie. record source) for the report, you should be able to link the two tables together by ClientID, add ClientName from the lookup table to the query, and then in the Report, view the Sorting/Grouping and add ClientName to the Sorting/Grouping box (selecting whether you want to sort Ascending or Descending.) Keep in mind that the Sorting/Grouping property for the report sorts by what fields are listed first, second, etc...(so if ClientID is in the Sorting/Grouping property box of the report, remove the ClientID row from this box.)

    I'm not sure what you mean by: Is there a way to change the field so the names can still be selected by a dropdown but we can sort by name? ..Are you refering to a form which is opened prior to the form? You don't have a dropdown box you can actually select values for when it comes to Access reports. Are you perhaps using Crystal Reports where you do have this option? Please be a little clearer on this.
    Last edited by pkstormy; 09-28-07 at 15:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2006
    Posts
    7
    I am sorry. I was thinking faster than I can type.

    The field in the Special Projects table called Client Name is a lookup table. I created this using the imported paradox database that has the ID and full name. I used both fields. I have a query that looks for any project not complete. I want to sort it by client name. It will sort by the client ID. I tried to create the lookup table by just using the client name but it still sorted by ID. I have my users entering the info in a form. (this is where the dropdown comes in) I want them to select the client from a list otherwise we would have all kinds of incorrect names in the database. There is info in the db now. I have to be careful how I change it now.

    Does this explain it better???? I am not a database person. I know just enough to want to hide.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    ClientName sorting

    Yes. I'm assuming you have 2 tables in question here....your main data table (which has the ClientID) and the lookup table (which has the ClientID and ClientName). Am I correct here as this is your typical relational structure? You store the corresponding ClientID from the lookup table in your main data table. If you were to design a query (for example), you would put both tables in the query and connect them by the ClientID. To sort your "Form" by ClientName, you can do the same thing for the recordsource of your form (disregard thinking about the combobox/dropdown box on the form - see below for the combobox sorting.) So your recordsource of the form has your main data table linked to the lookup table by ClientID. In the recordsource query of the form, you would have all the fields of your main data table and the ClientName field from the lookup table. Then simply select the sort row for the ClientName column (ie. either Ascending or Descending.) This will then sort the "form" by the ClientName.

    To illustrate how you would do this as well as sorting the combobox by ClientName, I quickly designed a small db to show you. Please see the attached zip file. You will notice that the MainEntryForm has a recordsource which shows the 2 tables and the sorting for the recordsource on the form is by ClientName so the form opens up with the records sorted by ClientName, ascending. You can easily save the recordsource query of the form to a "saved" query verses a query "embedded" into the recordsource of the form so you can open that query from other places (see example "QueryExampleSortedByClientName" query - ie. you can use this query for the recordsource of a Report to sort the report by ClientName.)

    Now getting to what I think is the answer to your question, notice the combo/dropdown box on the form and how I sort this combo/dropdown "ClientID" field by ClientName, ascending (see the rowsource for the combobox.) This is your typical way to setup a combobox which utilizes a lookup table (which I hope is the answer to your main question at hand.)

    So the example illustrates sorting all the records on the form by ClientName as well as sorting the combobox by ClientName.

    Also notice the query I threw in to tell you which ProjectCompleteDates are left blank (ie. not complete - "SampleQueryNoProjectCompleteDate".)

    As a little bonus, notice how I get the windows loginID in the EnteredBy field on the form - default value of this field =getuser(). This uses the Class module called: SystemInfo and another module called: UserName (Getuser). I always throw this into any db's I design as it's very easy to do. I wasn't sure if you already do this but again, it's very easy to do. All you need to do is import the SystemInfo and the UserName (GetUser) modules into any mdb's you design (feel free to use these) and you can set the default value of any field to =getuser() which will always return the current user loginID. To me the DateEntered and EnteredBy are essential fields for any main data table (notice the Default values of these fields on the form and when you go to a new record, it populates EnteredBy with your windows loginID.) I then always know who entered the record and when they entered it.

    Also notice the Relational design (ie. Tools -> Relationships) and how I have it setup so ClientID from the 2 tables are joined together. Again, this is your typical relational design for a lookup table to a main data table. Use this concept to setup any other lookup tables to your main data table.

    If I read you wrong and the attached db is not what you were looking to do, I apologize. It only took me a 4-5 minutes to design so let me know if it works for you. (I do know what you mean though on wanting to hide - I've been in that situation sometimes - working with databases can make you want to do that.)

    Hope this helps. I threw in a few extra stuff and I hope it doesn't make it confusing. I usually try to anticipate any future questions and think it's better to supply a good working example (and I can't help but throw in a few extras.)
    Attached Files Attached Files
    Last edited by pkstormy; 09-28-07 at 22:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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