Results 1 to 9 of 9
  1. #1
    jscalem is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    11

    Need help not sure what to call it :-(

    Hello,



    This is my first post. I know this must be possible however I am having problems. Someone anyone PLEASE any info is appreciated.......

    I have 3 spreadsheets I'm importing into access. These create 3 different tables (table 1, table 2, table 3).

    On table 1 I need some columns to look up data from table 2 & 3. Which I'm sure I can do with lookups. The parts I'm having issues with is that I have some columns which are yes/no. I need another column to update with the username when it's clicked yes and the next column to log the date when it is checked.

    The thing is there will be multiple people using this at different stages of a process. First to audit, then to QC and last to generate notices. I don't know how I can make a table or query that people can change the yes/no options and the comments. I need these to all connect back to table 1.

    I've tried doing queries but people aren't able to edit them. I've tried having people all working in table 1 but that doesn't work out. Can someone please help me?? Also for the last part the notices. They will be sending many at a time, so if they are able to click yes on many at once rather than having to click yes 100+ times.

    I hope this makes sense. I'm pulling my hair out please help!!!!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think your difficulty mainly lies in the fact that you don't seem to be aware that tables are not to be worked on/in directly. Table fields (not columns) are not meant to do anything based on an action taken in any other field. Lookup fields (if we're meaning the same thing) while possible, are also to be avoided.
    I don't know how I can make a table or query that people can change the yes/no options and the comments.
    You don't, actually. You use forms which are based on queries or tables, and the form and/or code behind the form manipulates data in the tables.
    Also for the last part the notices. They will be sending many at a time, so if they are able to click yes on many at once rather than having to click yes 100+ times.
    This could mean anything since you're not saying what they'd be clicking on.

    You need properly designed tables, properly related to one another, then queries upon which to base the forms. If you cannot edit, add or delete info via the query, don't expect a form based on that query to behave any differently, so I personally suggest designing a query that works first. Some queries are not editable by nature; e.g. Union or Totals, or the reason could be improper/inadequate relationships. The "many" side of a one to many relationship can also present editing challenges.

    So not sure what anyone can do for you at this point as not much information has been revealed. I suspect you need to research db normalization as a start....
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    jscalem is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    11
    Thank you guys. I understand and I've been a database that has forms, tables, queries etc. However it's possible I'm not explaining what I want to do properly. I will do some research and possibly post a better explanation.

  5. #5
    jscalem is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    11
    Ok so a little more clear to possibly get some direction. So I know where to research or suggestions on how to make this happen.

    I have spreadsheets that we want to import into access and create a database. I would put these in as tables.

    Table A- tickets pulled from a system which will have information appended to it weekly.
    Table B - A list of stations
    Table C - a list of contacts

    I need to be able to pull a list of information from table A that will pull matching data from table b & c. I understand doing this via a query. The thing is I need to once we have that list of tickets that need to be audited/qc'd. I need the agent to be able to go in and mark if it was valid or not, and enter in comments or their log in. Then the auditors will do the same.

    After all this is done I will use a query to be able to produce a mailmerge.

    The part I'm having issues with is being able to create something that will give the user the tickets that need to be worked and the ability to mark them as qc'd or with their log in and so on.

    Hopefully this is a better explanation of what I need some direction on. Just pointers or things to research or can I do this in a form or etc. please.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Describe in plain English what it is that brings Tickets, Stations and Contacts together in your "business".
    Perhaps you could provide us with "a day in the life of our business" so we have some idea of the subject matter involved and your understanding of the requirements.

    Rarely can you import Excel spreadsheet directly to Access as a Table. And getting the appropriate tables identified, designed, related and tested is a key first step with any database.

    Good luck.

  7. #7
    jscalem is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2017
    Posts
    11
    Ok so the first person pulls a report from our system with ticket numbers once a week. This is put into an excel workbook.

    This is then given to the auditors. They go into the system look up each of those ticket numbers and see if the information is accurate and determine if the audit is valid or not. If the audit for that ticket number is valid they indicate with a comment that it is valid. The list then goes to the QC team. They QC the ticket and decide if the auditors decision of valid/invalid was correct & indicate that on the spreadsheet.

    This list is next given to a team which filters out everything that both the auditor and qc rep marked as invalid. They then take that list of ticket numbers and look up the contact information, email, details from another spreadsheet. They then use that completed list of tickets to create a mailmerge of notices to send out to our clients saying these tickets were not valid.

    The spreadsheets are too big the sharing is becoming a problem & there is no visibility for reporting.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    A list of stations?

    So if I read between the lines, your company does some sort of audit/verification activities.

    Tickets seem to represent items to be audited? You mention Auditors and QC reps and QC teams in your latest post, but not in describing your initial issue? You mentioned Stations initially but not now?

    It also seems you have a three tier process regarding audits:
    -auditor reviews item, then
    -qc rep reviews item, then
    -qc team reviews item.

    You mention valid/invalid audit or tickets. Is there some criteria/category/features that separates these?

    Still don't know exactly what a ticket is??

    Good luck with your project.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't import the sheets, link them. They'll appear as tables which Access will "update" when it opens as long as the links are still valid. I can't figure out if the information you want someone to enter is in a field in one of these sheets or not. If not, you'll need to build a table that provides for these entries, having the field(s) you can link to the query you say you can build (or already have) based on the 3 sheets. You will probably need a query upon which to base a form through which a user can enter this data. If so, build that before the form and see if you can edit the required fields. If not, the query is probably not editable for one of these reasons:
    http://allenbrowne.com/ser-61.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. What do you call this?
    By mrmims in forum Access
    Replies: 4
    Last Post: 11-11-2015, 12:43 PM
  2. Call A function
    By aspen in forum Programming
    Replies: 10
    Last Post: 03-16-2014, 12:57 PM
  3. Call and phone
    By drunkenneo in forum Programming
    Replies: 0
    Last Post: 08-28-2013, 12:49 AM
  4. Not even sure what to call this one
    By eizquierdo in forum Queries
    Replies: 2
    Last Post: 03-14-2013, 12:08 PM
  5. how to call a sub procedure?
    By dollygg in forum Access
    Replies: 1
    Last Post: 08-18-2009, 05:10 AM

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