Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    3

    Exclamation Unanswered: Access - Username and Chronoloigal Queries

    Hey,

    I am creating a database to store part information and pricing history and I have two issues that I cannot seem to solve.

    First, I have an import form to import pricing information on a list of parts from Excel into a table in Access. I would like to append the windows user name onto each line of data to mark the person that uploaded that information. I already have code to pull the windows user name, but I cannot figure out how to put it into the table when someone is importing information.

    Second, I would like to create a query to pull back information on a part number. My issue is that I cannot figure out how to pull back complete lines from multiple different tables in order to display them chronologically. For example, a part may be entered into the Original Pricing table on 1/1/13, and a discount on that part may be entered on 3/1/13. I want to be able to search for a part number and have it pull back the line that includes the original pricing and a line that includes the discounted pricing. Perhaps this might require creating a new table that combines all tables in order to accomplish this, but I'd like to know if there's an easier way.

    Thank you for your help!
    Amandacolle

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For the first, it would help to know what method you use to import the data. For the second, what is your table structure? You seem to be saying each price would be in a different table, which would generally not conform to normalized design.
    Paul

  3. #3
    Join Date
    Jan 2013
    Posts
    3
    I set up an import form that allows the user to browse for the file they want to import from, enter the sheet name and cell range.

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Initial Pricing", filepath, True, SheetName & "!" & FromRange & ":" & ToRange

    When analysts price a part initially, they use an excel file to document it. That file will get loaded into the Initial Pricing table. When a regional analyst hears from the region that the price was too high, they make an adjustment, but also log the price review form they received from the region into the Price Review table. I would like to be able to do a search for a part number, and it pull back the part number, description, price and date from each line that part is listed.

    Does that make more sense?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    With that method, you'll have to add the name after the fact. One method would be to execute an update query:

    UPDATE TableName
    SET UserName = YourMethodToGetNameHere
    WHERE UserName Is Null

    Which would update any empty name. That would obviously not work the first time, as it would update all records. After that, the only Null records would be the ones just imported.

    You can union the two tables together to give you a way to pull all the records pertaining to a part:

    SELECT Field1, Field2
    FROM FirstTable
    UNION ALL
    SELECT Field1, Field2
    FROM SecondTable
    Paul

  5. #5
    Join Date
    Jan 2013
    Posts
    3
    Thank you! Now the way that I'm importing these sheets of information requires that the header of the column in Excel match the header of the column on my table. If they don't match, the info won't get imported. Do the columns of data that I want to union have to have the same title?
    Thanks again!

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No, they don't. The result set will show the names or aliases from the first SELECT statement. You could have this:

    SELECT Field1, Field2
    FROM FirstTable
    UNION ALL
    SELECT Field7, Whatever
    FROM SecondTable

    The results would look like Field1, Field2.
    Paul

Posting Permissions

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