Possible way to simplify a small issue; avoiding running append queries

[snipe]

Hey guys,

Whenever I want to design a report or program that involves the salespeople at my company, I have to run 2 separate append queries. This is because of how our point of sale system works. Attached is a snip of the main sales table from the ODBC. The circled columns are salesperson 1 and salesperson 2 (if the sale was split). The "DEL_DOC_NUM" shows the sale number. You can see that the first sale (and all of the items of it) are split between RR79130 and RA83560. As of now, if I want to build a report of of this, I have to run 2 append queries. 1 is everything you see in the pic, minus the 2nd salesperson column, and with a change to the PRC (price) columns multiplying them by IIF(SO_EMP_SLSP_CD2 is not null, .5, 1). I then run a second append query but remove salesperson 1 column, and use criteria "is not null" and salesperson 2 column. I use the same modifier on the price columns. I then run the rest of my queries off of that table.



I am just curious if there is a way, either with custom functions or something else I don't know of, to not have to do the 2 append queries any more? Some of our tables are enormous and the ODBC is based out of San Diego (I am in Dallas), so having to run 2 queries to the server can take quite a while.

This isn't a crisis or anything. Just hoping to learn something. Thanks guys

Snipe

[Xipooo]

Out of curiousity, why are you appending at all? Why not make the two queries select queries and then use those as subqueries for a third query which will display your results?

Part of the reason it may be taking so long is because the data is also being written to that temporary table, not just displayed. If you can do this as select queries, then you could also look at doing them as pass-through queries which will make it go much faster.

[snipe]

Well that's an easy answer, I honestly never thought of doing it that way. One of the benefits that I liked about using the table is during development, once I pull all the data in, manipulating it is super fast. I am about to start a new project tomorrow so I will give that a try.

[snipe]

Stuck.

I have pretty much recreated the original table. Look at the sale #02014050011, which is split.

What I want is:
-Only 1 column for "Salesperson"
-2 records for that sale, 1 with salesperson A, 1 with salesperson B

Note, I added "SplitModifier" column. This way, I can still glance and see the total value of the sale, whether or not if it was split. SplitModifier will be multiplied by LN_PRC to get each salespersons cut.

[snipe]

Also, I am on Access 2003, not 2010. I am using a VPN, not my local database.