Comparing fields that don't fall in same category

[wEnShEnG]

Hi, this is my first post, i need help on my problem [Excel 97, WinXP]

I need to compare fields in the xs with data in a access database. But these fields in the xs is not categorized properly, so how should i do it?¿
Take a look below of the fields i mentioned:

thanks a lot in advance...

 

[Ziggy]

Give some more details, Is the "Major" Partnumber just a single number associated with the components (as in a bill number). Are you trying to automate this?

Will the component match exactly to that in Access?

Initially I would sort the list and delete the descriptions, you have to clean it up so it is a proper "datalist" in "Record format".

Then you can import the list to access. Once there you can create a Union query (with the new table and the existing one)to compare you data.


Ziggy

 

[wEnShEnG]

I just want to compare all the part no with the access table data...

Can i ask how do i follow up with cleaning up with "Record Format"?
Is it within Excel or Access?

And how to go about doing a Union Query? i got no idea and never heard of it before..

Can you help me please?

 

[Ziggy]

your excel sheet has a product code on one line and the description for that product below it. a proper record would have them on the same row.

But you can just import it "as is" if you don't care about the descriptions,

Just import the excel sheet, then go to the query tab select new and from the table list select the new table and the table you want to compare to.

Now both tables may have ID depending on how you set them up. if there is a line between the 2 delete it and then click on one of the product code fields and drag a line to the other.

Add the fields you want to see data for. At this point the default Join (union) will return only the data that matches between the 2. if you right click on the join line (Join Properties) you can alter the results.

You can try it without cleaning up your data, Access will only return the matches, unless you choose the "join option" to return all the data from the new imported table.

If you need more clarification let me know, I do this all the time to compare 2 sets of tables it works.



Ziggy

 

[wEnShEnG]

oh, i want to see the output that shows only data in the imported table that the original table doesn't has...

that is to say, i want to see the extra component numbers in the imported table!

i need to automate this process of comparing and showing of data just but one click"CompareNow!"... do it with macros?

 

[Ziggy]

You will get the result you need by modifying the Join on the query.

You may be able to get away with formatting your "part" field (Number) so Access will reject the text (descriptions) and put them in a "import error" table.

Have you tried to obtain the data in any other formats, like CSV text files,ODBC , etc...

Unless someone can suggest something better, start experimenting with this so you can see the results then you'll know what direction to go in.

Post in the Excel forum, to see if someone can help you organize your data in record format if it proves to be a problem.

Ziggy

 

[wEnShEnG]

i need to do a macro right now... i did the query to show the output already... can i ask, after output is shown, can i immediately delete the table that is imported? i wan it to delete automatically... is it possible?

 

[Ziggy]

I do it one of 2 ways, either link the spreadsheet to Access in which case your (Excel)macro would clean up the file and save as the same file name.

Or in Access you need to set up Macros/VB to automate the import. You would set up a delete query to clear out the records in your table and then import (Transferspreadsheet Action) the table.

Ziggy

 

[wEnShEnG]

so u mean when i view the output i can delete the "imported TABLE" ?

i tried but the program says error because there is another person viewing...
that's why the macro can't work! is there anyway i can delete it?

if the table is not deleted, the same table will have lots of output because the previous person did not delete it...

 

[Ziggy]

No you can't delete the table when you are viewing it. I don't know what you need to do with the data, you can export it back to Excel or you can print a report or set up an "Append" Query to move the records to another table. It depends how you want to structure it.

Basically I suggest that when setting up the import Macro(Access), you set the events in this order:

Append to new table (optional)
Delete query
Import (transfer spreadsheet action)

This way you can leave the data as is and when you do your next import it will automatically delete the records and import the new ones.


Read up in the help files on the different types of queries and Macros so you will get a clearer picture of how to tie them together.