Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Universal Access updates (Access97-SR2)

    My boss has come up with the brilliant idea of wanting every operator to have the ability to update the status of jobs from their desktop.
    Problem 1 -Few have Access
    Problem 2 - It's a memo field
    Problem 3 - It's a mixed bag of PCs and MACs
    Haves
    The only common factor is that they all have Word and (Excel, which I use to track time), and a common network connection.

    Have Nots:
    We do not use Outlook, so sending out auto-reports based on employee doesn't seem like an option.
    Using the intranet for this data is also not an option with our autonomous ISD section. <img src=/S/argue.gif border=0 alt=argue width=50 height=25>

    The factors involved would be a Work Order # (WO)[There is also a WO_ID],
    employee number (Emp) and a (status) field.

    Here are their datatypes:
    WO=Text (e.g. A1234
    Emp=Long
    Status= Memo ( This is the crucial one- but I'm seriously considering converting this back to text) It is not a deal breaker!

    I'm thinking that even if I can grant them a common data portal, the real problem is confirming that the operator has the correct WO before updating.

    I hope I'm being clear...
    Does any of this sound familiar ?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Universal Access updates (Access97-SR2)

    I'm not clear on what Access has to do with it unless you plan to install the runtime on each machine, and you can't do that on the MACs.

    The memo field may very well be the deal breaker because they definitely *break* in Excel. You might be better off building a front end for them in VB, although I'm not sure where that leaves the MACS. To handle the mixed bag, you might be better off sticking with Excel and importing the results into a central Access database for analysis.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Universal Access updates (Access97-SR2)

    I had a feeling that would be confusing...Sorry.

    We would like operators(PC and MAC) to be able to update the status of their projects in the dB records.
    Naturally, I'm not going to give everyone access to the dB.
    Even if I would, I couldn't because most don't have Access.
    I want to compare a linked table consisting of a range in an Excel spreadsheet,[Table2] to the primary job table residing in the dB [Table1].
    There is already an existing series of spreadsheets that link these operators together, so this seems like a logical extension.

    Scenario: On Table 2(Excel-range), take the value of Column A(JobNO) and the value of Column E(Status), then compare it to JobNo and status in the dbTable[Table1]. I realize I may have to compare string values, Len would probably be a good boolean test, or if there was a way to time stamp Excel entries, that could also serve well as source of validation.

    I hope that helps clarify...
    I've seen what you guys do with mailclients, and API files.
    This must be bush league in comparison... Some day, for me too. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Universal Access updates (Access97-SR2)

    It sounds to me that if you are mucking around with different platforms and spreadsheets (linked??) eeek, I would go for a nice 'cross-platform' option that could interact with your database and doesn't need any software installe don the 'client' machines apart from a web browser ie. ASP.

    Of course, this relies on the fact that you have an intranet (or at least the ability to set one up with IIS) and someone who could write the asps (or the time to do/learn it yourself).

    This may be completely out of the question, but it is certainly what came to mind for me. I've done a couple of 'sites' / web applications now that interact with Access 2K databases just nicely.

    jayden

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Universal Access updates (Access97-SR2)

    Well, the ASP option is a sure fix, but I'm not sure that I could sell the pack in ISD into letting me have my own space.
    (they really frown on anything not approved by the head cheesy poofs)
    ..then again, code compliance and optimization IS their job.

    Problem is I don't have Access2k, so all those groovy plug-ins aren't available.
    FrontPage?-Sorry, Netscape is the official sponsor of the 2001 lowest bidder awards,
    so everyone that needs to use the system doesn't have IE.
    I'm not exactly thrilled with the way the browsers interpret different WYSIWYG generated HTML code either.
    Javascript is a larger nightmare.
    Data transfer seems like a very dim possibility under these circumstances.

    I really appreciate your assistance though. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Universal Access updates (Access97-SR2)

    OK, as long as you can link the Excel sheets to the database, you should be able to do it with a query, but you're going to have to handle the datatypes between the two tables somehow, especially if you try to join them on the JobNo. You'll either have to have text on both sides of the join or use an implicit join instead of an actual one. There's no way to reliably datestamp and Excel row that I know of, so you're going to have to use the values within the records to match apples to apples.

    That gives you a starting point, but you didn't say what you wanted to do from there. Are you trying to see if the same JobNo has the same Status in both tables? If you only want to see those that don't have the same status, then set criteria in your query to only return records where the JobNo is the same but the Status is different. The you can use that query as the basis for an update query to update your Access table from the spreadsheet.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Universal Access updates (Access97-SR2)

    <font color=red>OK, as long as you can link the Excel sheets to the database-</font color=red>

    I don't know if I can do that.
    They didn't install MS query in my unit- don't I need that?

    What are my options?

    The data itself is already matched. (text)
    It needed to be text to accomodate the wacky job numbering system my boss came up with.
    Use a letter as a prefix to a 4 digit number
    A#### - A = 2000 B= 2001 C = 2002 ( you get the idea)
    I said what happens when we hit Z?
    He replied that the system should be obsolete in 26 years anyway, or then it's someone else's problem.
    I explained that this was the exact same type of thinking that caused the y2k problem...
    Naturally, he was unaffected. <img src=/S/toilet.gif border=0 alt=toilet width=24 height=26>

    In the end, all I need to do is check that it's the same job number and update the status column
    if it needs it....setting that type of condition is the tricky part.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Universal Access updates (Access97-SR2)

    Why would you need MS Query? Access has its own query engine. The other Office apps use MS Query. You are doing this querying from Access, right?
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Universal Access updates (Access97-SR2)

    Yes, I would be doing this from Access.

    I never learned a query def, and I imagine that's what's going to go down.
    I can import speadsheets, but querying from one is another ballgame

    Doing all of this in ACC97 makes it tough to track down documentation.
    Thanks

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Austin
    Posts
    401
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Universal Access updates (Access97-SR2)

    I've got a procedure that exports the data in (Table A) to Excel with all the fields I need
    and then a linked table (Table [img]/forums/images/smilies/cool.gif[/img] which carries back to the dB.
    Then a query that compares the two.

    How can I define criteria in 1 field of Table B by string value,
    then use the outcome to update the same field in table A?

    I'm thinking a boolean test based on string length.
    Sound reasonable..or am I oversimplifying it?

    <img src=/S/electric.gif border=0 alt=electric width=15 height=15>

Posting Permissions

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