Seek some guidance on how to merge/link similar records

[peskywinnets]

So I'm slowly trying to create/deploy and order management system, to replace the 'off the shelf' version I['m using to manage my ebay orders.

I've got a fair way already (learned how to use Ebay's API & parse the XML into normalised data spread across several tables), but I'm suffering from brain fog & don't know how best to tackle this particular situation I'm now confronted with.

Quite often customers buy something from me, then an hour or two later they buy something else...occasionally they buy a third item...but not always in the same currency. So, to make processing/packing orders a little easier, I see away of combining incomings order from the same person (& in the same currency) into one 'combined' order (the software that I use calls this action merge').

Imagine this (grossly simplified) incoming Ebay order data...



Mr Jones has placed three orders. Two of them (those in green) are to the same address & placed in the same currency & can therefore be treated as one order (his last order - in red - is in a different currency & needs to be treated as a separate order as you can't combine orders in different currencies into one)...therefore I'd like the items to be combined into one order ...what would be the approach here?

 

[Ranman256]

I would have a form. In it, a list box that shows unique customer/address.
Select a customer,
The sub form will show all items based on the item in the list box. But same Currency.

Put a button on the form to 'combine' these by running an update query to assign the order# to all these.

 

[Minty]

Simply create a query to group the things that you can have that match, in your example you would group on CustomerName, Address1 and crucial to your example, Currency. This will pull the top level dispatch records you need.

 

[peskywinnets]

Thanks for all you input.

Simply create a query to group the things that you can have that match, in your example you would group on CustomerName, Address1 and crucial to your example, Currency. This will pull the top level dispatch records you need.

I probably wasn't clear/explicit enough.....what your suggesting will clearly group all the similar order together, but I ultimately need to create a sales receipt with everything on that has been 'grouped' so in the above example, we should end up with Mr Jones sales receipt like thus.

SKU............ItemPrice
widget A..........10
widget D...........7
Postage Cost......4
Order Total.....$21

(i.e. all his ordered items & postage combined together)

unless I've missed the cut & thrust of what you're saying? (just to set out my stall - I'm now getting pretty good with tables & queries, but reports & to a lesser extent forms - are still very new to me)

 

[Minty]

Yes - you can do this in a report easily. Create a query that brings in ALL the data you need. Then in the report you can group and total by the fields we mentioned earlier.

So your header would have the customers information details in it, and each line would be the order detail line below it. Your query would only need to be restricted to un-shipped / un-picked order lines.

 

[peskywinnets]

Thanks I'll check this line of discovery out (actually I think I dabbled with this a couple of night ago & got an error message when trying to group something along the lines of "subscript out of range"). ....I was trying to group a report based on a query pulling data out of several tables....

I would have a form. In it, a list box that shows unique customer/address.
Select a customer,
The sub form will show all items based on the item in the list box. But same Currency.

Put a button on the form to 'combine' these by running an update query to assign the order# to all these.

I#m taking baby steps here...that's a lot to take in, but I'll take each piece in turn & see how I go - thanks!