D
Dennis
I am working on a budget database and I am having trouble
querying data and getting the results I need. I have four
tables all with links/subdatasheets. Each table cascades
with the related field to the next table. The problem I am
having is, data is repeating in the query three or more
times in some of the records and only once (as I want all
fields to report). The relationship of these tables is
based on budget codes.
The first table's join is with an allotment number example
(1 or 2 or 3 or ...). The first table is the one and the
second table is the many. The second table joins with a
budget number example (1000 or 3000 or 3500 or ...). The
second table is the one and the third is the many. The
third table joins with an object code example (000 or 101
or 311 or 312 or ...). The third is a many and the fourth
is a many. This last table is where I believe the problem
begins. In all of the above tables the linking numbers are
a one to many and this last link is a many to many as the
numbers are not unique in either table. I believe that's
why I cannot get the results I need. When all of these
numbers are put together (Budget number, object code and
object detail number) they end up with unique numbers that
can be used to get the desired result which is to match
expenditures with budgeted money by matching these
numbers. The numbers do not come combined as they are
imported from another program and will not be changed. If
I am to make this budget application work I have to find a
way around the problem. I guess my question is what can I
do to achieve the desired result?
Budget #,#'s joined, Obj Code,Obj Detail, Description
1930 1930000000 000 000 Direct Materials
9389 9389000000 000 000 Material Burden
9709 9709000000 000 000 Outbound Freight
8060 8060000000 101 000 Other Intrst Income
8092 8092000000 101 000 A Other Non-Op Reve
9731 9731000000 237 101 New Enterprise
9740 9740000000 330 311 Interest <Expense>
9790 9790000000 330 311 Other Non-Op
Thanks,
Dennis
querying data and getting the results I need. I have four
tables all with links/subdatasheets. Each table cascades
with the related field to the next table. The problem I am
having is, data is repeating in the query three or more
times in some of the records and only once (as I want all
fields to report). The relationship of these tables is
based on budget codes.
The first table's join is with an allotment number example
(1 or 2 or 3 or ...). The first table is the one and the
second table is the many. The second table joins with a
budget number example (1000 or 3000 or 3500 or ...). The
second table is the one and the third is the many. The
third table joins with an object code example (000 or 101
or 311 or 312 or ...). The third is a many and the fourth
is a many. This last table is where I believe the problem
begins. In all of the above tables the linking numbers are
a one to many and this last link is a many to many as the
numbers are not unique in either table. I believe that's
why I cannot get the results I need. When all of these
numbers are put together (Budget number, object code and
object detail number) they end up with unique numbers that
can be used to get the desired result which is to match
expenditures with budgeted money by matching these
numbers. The numbers do not come combined as they are
imported from another program and will not be changed. If
I am to make this budget application work I have to find a
way around the problem. I guess my question is what can I
do to achieve the desired result?
Budget #,#'s joined, Obj Code,Obj Detail, Description
1930 1930000000 000 000 Direct Materials
9389 9389000000 000 000 Material Burden
9709 9709000000 000 000 Outbound Freight
8060 8060000000 101 000 Other Intrst Income
8092 8092000000 101 000 A Other Non-Op Reve
9731 9731000000 237 101 New Enterprise
9740 9740000000 330 311 Interest <Expense>
9790 9790000000 330 311 Other Non-Op
Thanks,
Dennis