Combining Imported Tables from QuickBooks (Using QB2Excel)

[cdmayers]

Hello,

My trouble currently is that when I import tables into my access database, the tables have multiple ID's that are the same denoting that they are apart of the same order. For instance, the sales orders I'm trying to import have multiple items on those orders. Each of those should be subbed to one order number but instead it will just list multiple lines that are the same besides what item, qty, price etc is being ordered. I need to figure out a way to filter the duplicates and turn them into a subform of one sales order number rather than multiple lines that are basically the same.

I can't seem to figure a way to get this to work.

[Bulzie]

Give an example of the data before it is imported, after it is imported(incorrectly), then another sample on how it should look if imported correctly.

[cdmayers]

The import is not the problem. Basically my problem is that I need to group items that are ordered under one sales order number rather than the same sales order number repeated multiple times denoting multiple items in access.

[cdmayers]

here is a snippet of what I'm talking about. the first 10 or so lines are the same but if you were to scroll over, they would just indicate different items under the same order number

[Bulzie]

Maybe do a maketable query, pull in the duplicated data such as OrderNumber, CustomerName, etc. and click on the Totals/GroupBy button to get rid of duplicates and then run it to create a table of unique order info records. Then you can delete those fields from the above table(except the OrderNumber if that is the linking field to the records and then you have 2 tables that will link. Is this a process you import daily as if so then this would be abit of work to do each day, might need a vba code solution.

Or you can leave the dup fields and put those fields in the Header of your form, then in the detail (continuous form) show all the order detail fields like quantity, etc.

[cdmayers]

In the end, I'm trying to make a "dashboard" of sorts where people can select the order to fulfill. The functionality in QB is not as good. So basically it would need to update every time a Sales Order was added which is quite often. I see what you're saying but the problem is they aren't necessarily duplicates, just apart of the same order but different lines of inventory. I need to group them rather than deleting them.

I suppose I was wondering if there was a way to set a filter for incoming data that would group based on the data coming in. Two tables are created with respect to QB, SalesOrder and SalesOrderLineItems. Some how they are linked. The sales order table only has one instance of the sales order number (or as its referred to, Reference Number), I wonder if there is a way to match the two tables that way? I don't know, pretty confusing for a newbie hah!

[cdmayers]

Just an update! I figured how to link them. Basically when everything was imported, QB broke it up into SalesOrderLineItems and SalesOrder. SalesOrder had only one instance of every sales order (as expected), I was able to create a relationship by taking the SalesOrderLineItems and creating an arbitrary auto number in front of it. After that I could just link it.

Thanks for you help! I'm using the info you gave me for reports, very helpful.


THANKS!