Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Unmatched Data of Two Tables (MS2000)

    Hi,

    Would you advise how to record / write a marco / VBA code on the Wizard for the Find the Unmatched Data. Is it possible to have the old data, ie previous month data stored in the database, and when I have a set of new data, I then compare the unmatched entries.

    Is it possible to import file of notepad nature?

    All helps are appreciate.

    thank you, FY

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Unmatched Data of Two Tables (MS2000)

    Welcome to Woody's Lounge.

    A query created with the Find Unmatched Query Wizard will display the current situation each time it is opened, so if you give the old and new data tables the same name each time, you won't need to change the query.

    If you have a text file that contains data in either fixed width format or in delimited format (fields separated by commas, or by tabs, for example), you can import it into a table in your database using File | Get External Data | Import..., and select Text Files in the Files of Type dropdown list. The extension of the text file should be .txt, .tab, .csv or .asc, otherwise Access won't be able to read the data.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Unmatched Data of Two Tables (MS2000)

    Hi Kun...welcome!

    Just to re-iterate what Hans has already said... There is no need to create a macro / VBA code to compare two data sets for mis-matches. All you need to do is have the query that was created by the Find Unmatched Data and run it each time you need to compare the two tables. As long as the table names do NOT change, the query will provide you with the unmatched data each time. You can see the query as the automation you require!
    Regards,
    Rudi

  4. #4
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Unmatched Data of Two Tables (MS2000)

    Hans, Thanks

    Every month, I will received a some statement which I need to reconcile against 2 source files, both in notepad format. What I would like to do is to create a Form which can perform the following :

    1) Import the file and compare it against the previous statement
    2) Find the unmatched entries or data using 1 or 2 variables ( show the unmatched data )
    3) input the relevant information / data into a new table ( duplicates and unmatched after filtering )
    4) sort in ascending order
    5) export to a template

    Currently we are doing it manually every month and there are about a total of 3000 ++ entries. I would like to write some marco or VBA code
    on it so that some part of it can be automate.

    Would appreciate yours assistance. thank you

    cheers, FY ( novice )

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Unmatched Data of Two Tables (MS2000)

    Hi Kun,

    Is it possible to provide some dummy data that simulates what the notepad file looks like that you need to compare. It would be useful to know what type of data you are using, how the data can be compared in order to find mis-matches and allow for providing you with better advice to tackle this project.

    Tx
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Unmatched Data of Two Tables (MS2000)

    Hi Kun,

    The text file that you attached is not in the proper format to import into Access. I would recommend that the text file be imported into Excel first, and then "cleaned up" to make a tabular or database table format from the data. In order to import data into Access, the information must be in a tabular format with valid column headings. You will probably find that Excel will be better suited to create this tabular structure, as well as fill in the blanks (auto-insert) cells with the value above. This can also be automated with macros to eliminate any tedeous repetition.

    Once the data is cleaned up, it could be sent to Access to run queries to find unmatched data, and generate printable reports of the data!

    How familiar are you with Excel and macro's?
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Unmatched Data of Two Tables (MS2000)

    The text file you have attached is a report that has no doubt been generated from a table or query in a database. If you can obtain this data file directly, it would be MUCH easier to process the data.

    If you need to work with the text file, you will have to write code to read the file line by line and parse the contents of each line. There is no general recipe for this, since different lines in the report have a different structure, so the code will be very specific, and to write it you will need to know exactly what each item in the report means.

  8. #8
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Unmatched Data of Two Tables (MS2000)

    I am not sure if I am able to access the data file directly. I will try. Assuming I can't, writing code for each specific line will be out for me as I am a novice in this. Maybe Rudi's idea would be more feasible now, however I will need help from you guys to program it in Excel and later move the file back to Access for comparison and other function before sending it out.

    Your guidance is much appreciate along the way.

    thanks, FY

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Unmatched Data of Two Tables (MS2000)

    I'm afraid it wouldn't be easy in Excel either, since the text file is not in a table format - it is neither fixed-width nor delimited. See if you can get the people who produce the report to give you some kind of export from the data - it can be an Excel workbook, or a .csv file, or a DBase file, etc. As long as it is in a table format, you can import it into Access without a lot of hassle.

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Unmatched Data of Two Tables (MS2000)

    Hi Kun,
    When you attempt to access the source data, try accessing the tabular data from the query or table. This data is much easier to work with due to its structure.

    If we are to revert to using excel, it would be great if you could attach a sample of which data you want to compare. The text file you attached in the previous post is fixed width and very "scrapy"! (Excuse the word - its difficult to know what you want to analyse!) If you can attach a sample of a more tabular structure and clarify what you need to compare, we can start to tackle a possible solution!
    Regards,
    Rudi

  11. #11
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Unmatched Data of Two Tables (MS2000)

    Hi

    Attached is a dummy file. Every month I will need to compare two file ( previous month vs current month ) for any new inclusion / data. If there are some new data in the current month file, I will need to include them in the report. While at the same time, delete those data which don't appear in the current month file but was in the previous month file. From experiences, monthly data / entries movement are about 3 to 6 . Can Access also highlight the mis-matched on both table so that I can decide whether the data are relevant for the report.

    Is it possible for Access to auto-insert text in the cells below the fill text cell above it until the next fill cell?
    example :

    Column 1
    Row 1 US
    Row 2 auto-insert US
    Row 3 auto-insert US
    Row 4 UK
    Row 5 auto-insert UK
    Row 6 auto-insert UK


    All helps are appreicate.

    thanks, FY

  12. #12
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Unmatched Data of Two Tables (MS2000)

    Hi Rudi, Hans

    While I am trying to get access directly to the data as mentioned earlier, shall we work on this in Excel? Hans, Pls advise where you want me to post...Excel or Access section?

    Rudi, What information do I need to provide to facilitate this?

    thanks, FY

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Unmatched Data of Two Tables (MS2000)

    If you mean the text file as attached earlier (now removed), it will be difficult, since - as remarked before - the text file does not contain data in a tabular format. You will need VBA code to process the file line by line; this code is not specific to either Access or Excel. You will have to provide very detailed and precise information on how to interpret each line of the file.

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Unmatched Data of Two Tables (MS2000)

    Data removed by HansV at the request of <!profile=kun>kun<!/profile>

    Hi Kun,
    The above is an example of your original txt file. Let me reference this and give you some guidelines that we will need in order to help you effectively.

    1. As Hans mentioned earlier, the txt file that you recieve is not in a format that can be used for comparison purposes. The format is to volatile, as its structure can change for each report you recieve.
    2. In order to run comparisons, the file you provide must be in a tabular format that can be used more effectively. Consider a database table, (or and excel list based spreadsheet). The data is set up in columns, and each column has a field title that describes the content of that column. A list of this nature can be used to sort, filter, compare, calculate.
    3. It is therefore necessary that you more up the "data trail" and see if you can aquire a tabular format of the data. Hans mentioned that the txt file you originally provided was a report that was drawn up from data in a DB. If you can track down the source of the report you will find the data, structured in a format that is easier to use for comparison purposes.
    4. Only once you can provide us this data can we take the process further and ask you what you want to compare...(ie, Market Values by date or variences between settled and traded values???)

    Please do not lose heart. We are quite willing to guide you and help you, but we need something to work with.
    Hope you can find something!
    Regards,
    Rudi

  15. #15
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Unmatched Data of Two Tables (MS2000)

    Hi Hans, Rudi,

    I have managed to get the report in Excel format, I have attached a dummy sample.

    1) I would like to compare two monthly files by products - previous month and this month. to check whether there are any new inclusion and exclusion for this month.

    2) Create a third file for the new inclusions.

    3) Create a fourth file for the exclusions.

    4) Any others suggestions.

    Thanks, kun

Page 1 of 2 12 LastLast

Posting Permissions

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