Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Error message if table holds wrong data (2002)

    Hi,

    I need advise and help on how to approach this situation. I have a table that has data imported from Excel on an ongoing basis. The info is imported into a temporary table and then a append query is used to import the filtered information into a permanent table. My staff keep importing the wrong office into the wrong table. I need to know if there is a way to stop the process if the office names are not the same. Presentyly the append query I have looks to be sure that data is not being duplicated based on [care_date] and [client_name]. This works fine. It becomes an issue when the wrong office is pulled in. I'd like to be able to have the append query (or whatever other means will work) look to see that the [off_name] fields are the same and if they are not the import is stopped and the user is prompted telling them they are pulling in the wrong office and do they wish to view a report based on the data, but not store the data in the permanent database. The other thing that would need to be taken into consideration is that there will be times when there is no data in the table, but the data should be allowed to be pulled in.

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Error message if table holds wrong data (2002)

    It's not clear to me what you mean when you say
    <hr>I need to know if there is a way to stop the process if the office names are not the same.<hr>
    Is the issue that the data being imported have different Office Names in the various records, or is it an issue where the data being imported into a given table should always have the same Office Name, and you are concerned about someone importing data for some other office into the table that is supposed to store data for OfficeXYZ? If it's the latter, you could make the Office Name part of the Append query criteria, and reject all records not for that Office Table.

    I also don't understand what
    <hr>The other thing that would need to be taken into consideration is that there will be times when there is no data in the table, but the data should be allowed to be pulled in.<hr>
    means. Is this a situation where a given table isn't dedicated to a given Office until there is data in it, or what? If it is, your database schema is suspect - in fact I would place all of the data into a single table, store the Office Name in the table, and then use queries to isolate the data for a specific Office Name. But I'm probably oversimplifying.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error message if table holds wrong data (2002)

    Hi Wendell,

    I don't blame you for being confused as its not easy to explain. But, you've pretty much got the picture! This is the issue:

    __________________________________________________ __________________________________________________ ________
    or is it an issue where the data being imported into a given table should always have the same Office Name, and you are concerned about someone importing data for some other office into the table that is supposed to store data for OfficeXYZ? If it's the latter, you could make the Office Name part of the Append query criteria, and reject all records not for that Office Table.
    __________________________________________________ __________________________________________________ _________

    In this instance the records being appended will all have the same office name so either all records will be imported or all will be rejected. What I'm looking for is a way to let the user know they tried to import the incorrect office and it was rejected.

    The answer to this..........Is this a situation where a given table isn't dedicated to a given Office until there is data in it, or what?...........is yes. I had considered your solution when initially working on this however I don't feel it would work in this situation. The database will reside in multiple branch offices as well as the main office. The branch offices will only be pulling in data from excel that is specific to their office so in those instances I'm fine. They would never have access to the other office names. The main office is my issue. They have access to all branch office data as well as their own. The database in the main office should be specific to that office only, however the supervisors, in wanting to oversee the other office data, keep pulling branch office info the database which is having an bad effect on that office's stats. My goal was to not set up one database for the Main office and another for the branches.

    So, how do I alert the user if their data was rejected due to office names not matching. I've turned off the access alerts. And, they are so such that the user won't clearly know why records were rejected.

    Leesha

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Error message if table holds wrong data (2002)

    <hr>The database in the main office should be specific to that office only, however the supervisors, in wanting to oversee the other office data, keep pulling branch office info the database which is having an bad effect on that office's stats. My goal was to not set up one database for the Main office and another for the branches. <hr>
    So what you will end up with is a database for each of the branch offices in the main office. Isn't that worse than having just two, one for branches and one for main. In any event, you are faced with multi-user issues and lots of other concerns. Rather than trying to restrict the input of data to a given subset, restrict the data being reported on by limiting it to a specific Office name. Then you won't be corrupting data with the import process, and multiple users can work with the data at the same time.

    To do what you suggest would require a good deal of VBA code to read the file from Excel and check to see if the Office name was for the correct database, and then to issue an error message to the user. User's will be confused and irritated by it as well - trust me - I've designed systems to do very similar tasks, and if the user simply want to generate a report they get really testy when they have a complex process that is error prone.
    Wendell

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error message if table holds wrong data (2002)

    __________________________________________________ __________________________________________________ _____
    So what you will end up with is a database for each of the branch offices in the main office. Isn't that worse than having just two, one for branches and one for main.
    __________________________________________________ __________________________________________________ _____

    No, the goal is that each office including the main office have separate databases. The problem occurs in the main office because they have access to all the databases for the branch offices. Again, I have restricted the data to a given office, I'm looking for a way to alert the user that they have tried to install the wrong office into the database.

    Leesha

Posting Permissions

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