Complex Design

[Guest]

I am designing a new data base that contains issues (to resolve). The table
I designed as all the information for the basic issue. What I need to know
is how to "attach" additional information to and issue. Such as

issue 561 is stock number 123456 I need to add (as it progresses) the
solutions associated with that particular issue. I also need to keep all the
General Ledger accounts that the issue affected. I then need to be able to
track the correction of the issue and the general ledger accounts that the
correction was made to.

I think what I need to do is have tables associated with the orginal issue
which is in a table...... I have no clue how to do that...Can anyone out
there help

 

[John Vinson]

On Thu, 26 Jan 2006 10:30:03 -0800, "A Marker" <A

I am designing a new data base that contains issues (to resolve). The table
I designed as all the information for the basic issue. What I need to know
is how to "attach" additional information to and issue. Such as

issue 561 is stock number 123456 I need to add (as it progresses) the
solutions associated with that particular issue. I also need to keep all the
General Ledger accounts that the issue affected. I then need to be able to
track the correction of the issue and the general ledger accounts that the
correction was made to.

I think what I need to do is have tables associated with the orginal issue
which is in a table...... I have no clue how to do that...Can anyone out
there help

Well, that's the most very basic example of how relational databases
work: two tables in a one to many relationship.

You need a table of Issues, with an IssueNumber as its Primary Key.
Then you need a table of Solutions, with its own primary key (perhaps
an autonumber SolutionID) and a Foreign Key field - a field of the
same datatype as ProblemID, or a Long Integer if that's an autonumber
field. You would use the Relationships window to define a
relationship between the two tables, joining on ProblemID; and a Form
based on Problems with a Subform based on Solutions to view and edit
the data.

I'm not clear on how the General Ledger fits into this. Does a given
problem relate to one General Ledger entry? or several? And does a
given Ledger entry relate to only one problem, or might it be related
to many? Do you have a table of stock numbers? What's the relationship
between stock numbers and problems - might Stock Number 123456 have
six or eight issues, or will it have only one? Will a problem always
involve one and only one stock number, or might a problem cover
several?

Check out some of the resources at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
particularly the "Getting Started" and "Database design 101" links.
There's a rather steep learning curve but you'll find that the
relational model is very powerful and will be able to get your job
done!

John W. Vinson[MVP]

 

[Guest]

John,

Thank you for your help.

The general ledger would have several entries

ex.

sales $180
inventory $200
cogs $200
discounts $20

It can actually have up to 8 accts associated with one Issue. Usually and
Issue will contain one or more stock numbers. We work on a Moving Average
Cost. So what might happen is that a stock number at several locations may
have a MAC that is bad. Therefor I need to track that stock number at
several locations. Now an example of this is we have 99,000 stock numbers
that have a zero mack. This would be one issue....say issue 56. I would
need to track every locations mac for that particular stock number. And each
Location would have its own general ledger activity that could be up to eight
accounts for one entry. When they reverse the entry that caused the MAC to
go to Zero. It needs to match the orginal entry, so that it hits the orginal
accounts that made the mistake.

so to sum up...one issue, can have many stock numbers along with many
locations with a particular issue. The issue number is the key. Figuring
out how to put 1) general ledger entries for each location 2) Listing all
the Locations that effect a issue. 3) Being able to log the corrections and
verify that it matches the exact accounts that were effected and be able to
cross reference it.

Thanks for any help you can give.

angie