Results 1 to 5 of 5
  1. #1
    poetstorm is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Aug 2019
    Posts
    2

    clookup wizard on a field that is also a primary key? Help Pls

    Hi! I am semi-new to access and I am sure this can't be hard but I can't seem to find anything that applies. I am probably not using the right search terms.

    So I have two tables. One is a list of customers, and the other is a list of billing rates by type of service.

    The primary key is client name in the customers table. And it links to client name in the billing rates table as a one to many relationship.

    When adding new rates, I want to limit the entry into the client field to match the data on the customers table. In other words I want to make sure that we are typing the client name correctly and that any rates we enter will match up to existing customers.

    So now I understand you can do that generally by changing the data type to Lookup Wizard, however there is where I am hitting a snag. Because this field is a relational field, I am getting an error because now the data type for the promary key field which is Short Text will nol longer match if I change the type.

    "You can't change the data type or field size of this field. It is part of one or more relationships."

    Does that make sense? I hope I am explaining it right.

    So how do I limit the entries only to existing customers without changing the data type? Thanks!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You should have a table of customers and one for rates. Then a junction table that links customers to rates, because rates are one thing, customers are another (their own tables) but customers can have several rates, thus the junction table. The junction might have customer id and rate id plus have a PK index (autonumber would suffice) of its own.
    On a form, header section can have customer info (not rates) with a subform below that lists the rates. You should be able to add or edit detail records in the subform if you set the link Master/Child property so that the customer id of the junction matches the customer id of the main form.

    You could also list rates in a listbox for each customer on the main form and forget the subform, but you would need a way to edit/add records. Main/sub form relationship is a common topic.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    poetstorm is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Aug 2019
    Posts
    2
    Hi, thanks! Yeah our business is way more complex than that I think. Our rates are really complicated which is why I have a whole table dedicated just to billing rates. They can depend on about a dozen different criteria. I just really need to find a way to make sure when people are entering the rates that they are entering customers that already exist and not making typos etc..

    It's possible I don't understand. I am really, really new. So I can't really see how this would help me right now.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    which is why I have a whole table dedicated just to billing rates
    I did say to have a rates table, so I don't get that.
    They can depend on about a dozen different criteria.
    The linking table probably takes care of that.
    I just really need to find a way to make sure when people are entering the rates that they are entering customers that already exist and not making typos etc..
    Customers table takes care of that. You don't need to allow typing in customer names - you pick them from a combo. Subform lists rates for that customer. There are as many ways to provide for adding rates for a customer as there are freckles on Alfred E Neuman's face.
    Wondering if what you need to do is bone up on the topics of normalization and linking tables?

  5. #5
    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 advice re normalization, I recommend that you work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the Database Planning and Design link in my signature. For your own sanity, don't jump into physical MS Access without a clear description of what you are trying to automate/support with this database. Do your analysis and design to understand the subtleties of what you are trying to do.
    Avoid Lookup Wizard. (search this forum for info on Lookup Wizard).
    Good luck with your project.

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

Similar Threads

  1. Replies: 7
    Last Post: 02-25-2019, 10:09 PM
  2. Replies: 10
    Last Post: 04-27-2016, 10:24 AM
  3. Replies: 3
    Last Post: 05-31-2014, 11:37 AM
  4. Replies: 3
    Last Post: 03-07-2013, 02:29 PM
  5. Using tbale field in Lookup Wizard
    By dotancohen in forum Database Design
    Replies: 5
    Last Post: 12-10-2009, 02:54 AM

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