Karl,
Since my original post and testing other things and researching more, I came
to the conclusion that I needed to go in another direction since it didn't
appear I could accomplish what I was trying to do previously. Now I have a
table that I have an Autonumber field so that I can run the DSum to achieve
the running total. Now I need to figure out how I can convert the DSum into
something that will run faster and produce the same result. Here is the table
layout and the SQL with the DSum.
Table - TEST_REMAIN_A
Deal - Text
PO # Text
Item - Text
SN - Text
Rank - Number
SumOfALLOC - Number
REMAIN - Number
LREMOVE - Text
PLACE - AutoNumber
SQL
INSERT INTO REMAIN_TEST ( Deal, [PO #], Item, SN, ST_TYPE, Rank, REMAIN,
LREMOVE, RunTotal )
SELECT TEST_REMAIN_A.Deal, TEST_REMAIN_A.[PO #], TEST_REMAIN_A.Item,
TEST_REMAIN_A.SN, TEST_REMAIN_A.ST_TYPE, TEST_REMAIN_A.Rank,
TEST_REMAIN_A.REMAIN, TEST_REMAIN_A.LREMOVE,
DSum("LREMOVE","TEST_REMAIN_A","PLACE<=" & [PLACE] & "And Item = '" & [Item]
& "'") AS RunTotal
FROM TEST_REMAIN_A;
I am still in the development stage and know I need to clean some of it up.
I am trying to get the database to run faster and have accomplished that with
the exception of the query that contains the DSum.
Sorry if it seems that I have been all over the place, just trying to get
this to work.
Thanks - accesshacker
KARL DEWEY said:
Where are you using the DSum?
You keep changing what you call things --
Table #1
ALLOC_UNITS
Table #2
ITEM_ALLOC_TEST
TEST_REMAIN_A
ITEM_ALLOC_TEST
NEG_ALLOC_UNITS
--
Build a little, test a little.
:
Karl, sorry for the confusion. I did answer which field in Table 2. It will
be TALLOC. This will be the initial distribution of units to all stores based
on the units in the PO. There is a front end that will determine the Per
Store Unit average and then a formula that determines what percentage of the
PSU will be allocated to an A, B, C type store. Example
PSU = 39
A stores will receive 1.5 times the PSU (5 A Stores)
B stores will receive 1.1 times the PSU (13 B Stores)
C stores will receive 0.4 times the PSU (21 C Stores)
I have developed a work around that involves additional queries and place
holder tables. I have in one of the queries a DSum based on certain criteria
(see below)
RunTotal: DSum("LREMOVE","TEST_REMAIN_A","PLACE<=" & [PLACE] & "And Item =
'" & [Item] & "'")
I have tested against a PO that had 90 Items and it works. The problem now
is that it is taking the DSum too long to run. Is there a way to make it run
faster, such as by putting it in a function?
:
I keep asking 'What field in table 2 is it being add to?' and you do not
answer the question.
What are your business rules? If you have 5 to distribute what formula do
you use.
Stores that sell the most - in value? - in items?
Stores that received shipments the longest time ago? Lowest stock level?
--
Build a little, test a little.
:
It will be added to TALLOC in Table #2
Here is the SQL
SELECT ITEM_ALLOC_TEST.Deal, ITEM_ALLOC_TEST.[PO #], ITEM_ALLOC_TEST.Item,
ITEM_ALLOC_TEST.SN, ITEM_ALLOC_TEST.ST_TYPE, ITEM_ALLOC_TEST.Rank, FNEGR() AS
ADDL
FROM ITEM_ALLOC_TEST INNER JOIN NEG_ALLOC_UNITS ON (ITEM_ALLOC_TEST.Item =
NEG_ALLOC_UNITS.Item) AND (ITEM_ALLOC_TEST.[PO #] = NEG_ALLOC_UNITS.[PO #])
AND (ITEM_ALLOC_TEST.Deal = NEG_ALLOC_UNITS.Deal)
GROUP BY ITEM_ALLOC_TEST.Deal, ITEM_ALLOC_TEST.[PO #], ITEM_ALLOC_TEST.Item,
ITEM_ALLOC_TEST.SN, ITEM_ALLOC_TEST.ST_TYPE, ITEM_ALLOC_TEST.Rank, FNEGR()
HAVING (((ITEM_ALLOC_TEST.ST_TYPE)="C"));
I have been working on a function called FNEGR to see if I can start the
field at 0 and if REMAIN is <> 0 then to either make FNEGR a 1 or -1 and
continue adding until FNEGR equals REMAIN. I have not been able to figure out
how to do this one row at time.
:
I was trying to add the 1 in the query until it equals the 20 and then stop.
What field in table 2 is it being add to?
How about posting the SQL of your query.
--
Build a little, test a little.
:
The query I have been working on is populating the 1. No fields are being
reduced to zero. Table #1 is only a holder for what is remaining. I am
testing right now and only using one Item from one PO#. There are a total of
1,753 items across several PO#s. I have allocated the majority of units and
in some instances still have some remaining that need to be allocated to
certain Store Types. If I can get this to work, I can expand it to all. That
is were the remaining can be from 1 - 38. I was trying to add the 1 in the
query until it equals the 20 and then stop. Would then append the data to
table #2 so that I can create a report that shows the allocation by
Store/Store_Type.
Thanks
:
Currently, it populates all 21 with 1.
I do not see anything populated with one.
What field(s) in table1 gets reduced to zero? Units or REMAIN?
What field in table2 has a quanity from table1 add to it?
--
Build a little, test a little.
:
Table #1(ALLOC_UNITS) Table #2(ITEM_ALLOC_TEST)
Fields/Type Fields/Type
Deal – Text Deal – Text
PO# - Text PO# - Text
Item – Text Item - Text
Units - Number SN – Text
Remain – Number T_TYPE – Text
Rank – Number
TALLOC - Number
Data Table #1
Deal PO # Item Units REMAIN
CARO CARO-42 5023701 167 20
Data Table #2
Deal PO # Item SN ST_TYPE Rank TALLOC
CARO CARO-42 5023701 101 C 15 7
CARO CARO-42 5023701 11 C 7 7
CARO CARO-42 5023701 13 C 8 7
CARO CARO-42 5023701 15 C 13 7
CARO CARO-42 5023701 17 C 5 7
CARO CARO-42 5023701 2 C 2 7
CARO CARO-42 5023701 21 C 10 7
CARO CARO-42 5023701 26 C 3 7
CARO CARO-42 5023701 30 C 12 7
CARO CARO-42 5023701 31 C 19 7
CARO CARO-42 5023701 32 C 6 7
CARO CARO-42 5023701 33 C 4 7
CARO CARO-42 5023701 35 C 11 7
CARO CARO-42 5023701 36 C 16 7
CARO CARO-42 5023701 37 C 9 7
CARO CARO-42 5023701 39 C 18 7
CARO CARO-42 5023701 4 C 21 7
CARO CARO-42 5023701 44 C 17 7
CARO CARO-42 5023701 45 C 1 7
CARO CARO-42 5023701 48 C 14 7
CARO CARO-42 5023701 6 C 20 7
I was hoping to be able to write a query that had the same fields as in
Table #2, minus the TALLOC, and has a field called 'Addtl'. Into this field
would be added 1 until it equaled the REMAIN in table #1 and then would
either leave a NULL or zero. This would be an Append Query that I would use
to update Table #2 so that the total of TALLOC would equal Units, Currently,
it populates all 21 with 1. (In this example there are 21 Stores, there can
be up to 39)
:
Post the table name, field names, datatype, and sample data.
How do you expect to allocate 20 into 39? 5 into 39?
--
Build a little, test a little.
:
Need to create an update query that will add records to a table that when
summarized equals the amounts in another table when compared. I know how many
units I need to add, the problem comes in when I need the update query to not
over allocate the units. Basically, in one table I know I have one record
that shows 20 units that I need to distribute evenly. I need to add a 1 unit
at a time until I have added the 20, then I need to stop adding records.
Depending on the criteria, the units remaining can range from 1 to 20. Is an
Update Query the way to go or do I need to look at something else. I do have
3 unique fields in each table that I can join on. There are 39 records that I
am allocating to.
Hope I have provided enough information, thank you in advance
