A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Complex Design



 
 
Thread Tools Display Modes
  #1  
Old January 26th, 2006, 07:30 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Complex Design

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
  #2  
Old January 26th, 2006, 08:13 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Complex Design

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

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/conrad...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]
  #3  
Old January 26th, 2006, 10:16 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default Complex Design

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

"John Vinson" wrote:

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

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/conrad...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]

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i apply an Axis design or radial design from design templa. nadia Powerpoint 1 April 3rd, 2005 02:21 AM
Seemingly (and probably) complex turnaround time rgrantz Running & Setting Up Queries 1 March 21st, 2005 10:19 AM
Design Templates don't apply font sizes consistantly Greg H Powerpoint 1 September 15th, 2004 02:07 PM
design master problem J. Vermeer General Discussion 0 September 8th, 2004 03:23 PM
opening a document so it is NOT in design mode Brad Pears New Users 1 May 3rd, 2004 09:13 PM


All times are GMT +1. The time now is 06:27 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.