Prev: learning access
Next: I have chopped head off picture.How do I restore orginal?
From: Terrence Carroll on 17 Feb 2010 10:17
I need to come up with a good design for a database addressing client payment
discrepancies. The main goals that I have are to track the number of issues
generated along with resolution status. I have an output file displaying
payment discrepancies by line item on an invoice (i.e. one invoice could have
many line items). Fields for the output file are as follows:
Customer Name, Account Number, Customer Number, Employee Name, Week of
Service, Invoice Number, Invoice Line Item, Date Payment Posted,Total Open
Account Receivable for invoice, Accounts Recevable Analyst


I figure the output file will be its own separate table. What I need to do
is come up with a way to assign an issue number to each line item. The
problem that I have is each line item on the invoice could theoretically have
more than one issue (i.e. sales tax was misapplied and client was billed at
the incorrect bill rate). Also one issue could apply to multiple invoices.
Therefore I do not think I can use a one to many or one to one relationship
between line item on invoice and the issue #. Can I get some tips on what my
"Issues Table(s)" should look like from a design standpoint or should I have
multiple tables due to the many to many relationship that is apparent in
order to come up with a normalized database design?

Thanks,

Terry Carroll
From: John W. Vinson on 17 Feb 2010 11:51
On Wed, 17 Feb 2010 07:17:01 -0800, Terrence Carroll
<TerrenceCarroll(a)discussions.microsoft.com> wrote:

>I need to come up with a good design for a database addressing client payment
>discrepancies. The main goals that I have are to track the number of issues
>generated along with resolution status. I have an output file displaying
>payment discrepancies by line item on an invoice (i.e. one invoice could have
>many line items). Fields for the output file are as follows:
>Customer Name, Account Number, Customer Number, Employee Name, Week of
>Service, Invoice Number, Invoice Line Item, Date Payment Posted,Total Open
>Account Receivable for invoice, Accounts Recevable Analyst

You're misunderstanding how Access works!

You do NOT need to pull everything together into one table in order to output
it. Typically you would instead have a table of Customers (the only table
containing the customer name), another table of Employees (the only table
containing the employee name), a table of Invoices (with invoice number,
service date, etc.), a table of InvoiceLineItems related one to many to
Invoices, etc.; the "total account receivable" value would not exist in ANY
table, but would be calculated on the fly. Any Report or Export would be based
on a QUERY, not on a table, and the query would pull data together from
multiple tables.

>
>I figure the output file will be its own separate table. What I need to do
>is come up with a way to assign an issue number to each line item. The
>problem that I have is each line item on the invoice could theoretically have
>more than one issue (i.e. sales tax was misapplied and client was billed at
>the incorrect bill rate). Also one issue could apply to multiple invoices.
>Therefore I do not think I can use a one to many or one to one relationship
>between line item on invoice and the issue #. Can I get some tips on what my
>"Issues Table(s)" should look like from a design standpoint or should I have
>multiple tables due to the many to many relationship that is apparent in
>order to come up with a normalized database design?

You need a table of Issues (e.g. IssueID 1 might be "Item cost entered
incorrectly", 39 might be "Sales tax was charged although customer was tax
exempt), related one to many to a table of ItemIssues; this would have fields
for the IssueID and for the primary key of the InvoiceDetails table. It would
also have fields recording how this particular issue was resolved (e.g. amount
refunded, resolution date, comments...).

--

John W. Vinson [MVP]
From: Terrence Carroll on 18 Feb 2010 10:21
John,

I really appreciate the help but the issue that I have is that I already
have the invoice detail data based upon a report extracted from a different
system so I am not really looking to create new table for the invoice data or
calculate any of the invoice related info such as AR balance or have the user
input invoice related data. The main objectives that I have are to try to
maintain statistics on how many new payment issues are generated and how
many issues are resolved, and what the timetable is for resolution. I do not
have the ability or resources to modify the existing system to track the
issues so I figured Access would be good tool to use to accomplish my goals.

For a particular week I may have 5000 invoice line items with either short
payments or over payments (i.e. discrepancies) from a number of different
companies. The following scenarios may be evident within the population of
the 5000 invoice line items:

1. Company may have a payment discrepancies on all invoice line items for a
particular period due to differences in how we and they apply contractually
based terms such as discounts and bill rate factors - I need to have an
issue table that allows me to assign an individual issue # to muliple
invoices. - My initial thought proces was to set up a one to many
relationship between my issues (one side) and the invoice line items (many
side) but I do not think I can do this because
2. Individual invoice line items may have multiple issues that require
different steps to resolve (i.e. customer is exempt from sales tax but never
furnished an exemption certificate (external issue) and customer has a
discount that is not being applied appropriately in our system (internal
issue))

I absolutely do not want to have fields with Issue #1, Issue #2, and Issue
#3 because I know it is not a good relational design and it wastes space.
However, I would like to have the database design set up so that when I have
an update to progress notes for a particular issue that the update would
cascade to all affected line items. Any help you or someone else could
provide would be much appreciated.

Thanks,

Terry Carroll
"John W. Vinson" wrote:

> On Wed, 17 Feb 2010 07:17:01 -0800, Terrence Carroll
> <TerrenceCarroll(a)discussions.microsoft.com> wrote:
>
> >I need to come up with a good design for a database addressing client payment
> >discrepancies. The main goals that I have are to track the number of issues
> >generated along with resolution status. I have an output file displaying
> >payment discrepancies by line item on an invoice (i.e. one invoice could have
> >many line items). Fields for the output file are as follows:
> >Customer Name, Account Number, Customer Number, Employee Name, Week of
> >Service, Invoice Number, Invoice Line Item, Date Payment Posted,Total Open
> >Account Receivable for invoice, Accounts Recevable Analyst
>
> You're misunderstanding how Access works!
>
> You do NOT need to pull everything together into one table in order to output
> it. Typically you would instead have a table of Customers (the only table
> containing the customer name), another table of Employees (the only table
> containing the employee name), a table of Invoices (with invoice number,
> service date, etc.), a table of InvoiceLineItems related one to many to
> Invoices, etc.; the "total account receivable" value would not exist in ANY
> table, but would be calculated on the fly. Any Report or Export would be based
> on a QUERY, not on a table, and the query would pull data together from
> multiple tables.
>
> >
> >I figure the output file will be its own separate table. What I need to do
> >is come up with a way to assign an issue number to each line item. The
> >problem that I have is each line item on the invoice could theoretically have
> >more than one issue (i.e. sales tax was misapplied and client was billed at
> >the incorrect bill rate). Also one issue could apply to multiple invoices.
> >Therefore I do not think I can use a one to many or one to one relationship
> >between line item on invoice and the issue #. Can I get some tips on what my
> >"Issues Table(s)" should look like from a design standpoint or should I have
> >multiple tables due to the many to many relationship that is apparent in
> >order to come up with a normalized database design?
>
> You need a table of Issues (e.g. IssueID 1 might be "Item cost entered
> incorrectly", 39 might be "Sales tax was charged although customer was tax
> exempt), related one to many to a table of ItemIssues; this would have fields
> for the IssueID and for the primary key of the InvoiceDetails table. It would
> also have fields recording how this particular issue was resolved (e.g. amount
> refunded, resolution date, comments...).
>
> --
>
> John W. Vinson [MVP]
> .
>
From: Steve on 18 Feb 2010 15:20
Hello Terrence,

I provide help with Access, Excel and Word applications for a modest fee. I
have provided help with the initial design of many databases. I would like
to offer to work with you to come up with a design for your database that
will work for you. My fee would be very reasonable. Contact me if you want
my help.

Steve
santus(a)penn.com


"Terrence Carroll" <TerrenceCarroll(a)discussions.microsoft.com> wrote in
message news:8B2CDFCC-ECE9-4624-B6D0-B46AB30FF005(a)microsoft.com...
>I need to come up with a good design for a database addressing client
>payment
> discrepancies. The main goals that I have are to track the number of
> issues
> generated along with resolution status. I have an output file displaying
> payment discrepancies by line item on an invoice (i.e. one invoice could
> have
> many line items). Fields for the output file are as follows:
> Customer Name, Account Number, Customer Number, Employee Name, Week of
> Service, Invoice Number, Invoice Line Item, Date Payment Posted,Total Open
> Account Receivable for invoice, Accounts Recevable Analyst
>
>
> I figure the output file will be its own separate table. What I need to
> do
> is come up with a way to assign an issue number to each line item. The
> problem that I have is each line item on the invoice could theoretically
> have
> more than one issue (i.e. sales tax was misapplied and client was billed
> at
> the incorrect bill rate). Also one issue could apply to multiple
> invoices.
> Therefore I do not think I can use a one to many or one to one
> relationship
> between line item on invoice and the issue #. Can I get some tips on what
> my
> "Issues Table(s)" should look like from a design standpoint or should I
> have
> multiple tables due to the many to many relationship that is apparent in
> order to come up with a normalized database design?
>
> Thanks,
>
> Terry Carroll


From: Stop$teve on 18 Feb 2010 18:54

"Steve" <notmyemail(a)address.com> schreef in bericht news:uv8nDfNsKHA.5036(a)TK2MSFTNGP02.phx.gbl...
> Hello Terrence,
>
> I provide help with Access, Excel and Word applications for a modest fee. I have provided help with the initial design of many
> databases. I would like to offer to work with you to come up with a design for your database that will work for you. My fee would
> be very reasonable. Contact me if you want my help.
>

--
Get lost $teve. Go away... far away....

Again... Get lost $teve. Go away... far away....
No-one wants you here... no-one needs you here...

This newsgroup is meant for FREE help..
No-one wants you here... no-one needs you here...
OP look at http://home.tiscali.nl/arracom/whoissteve.html
(Website has been updated and has a new 'look'... we have passed 11.000 pageloads... it's a shame !!)

Arno R


 |  Next  |  Last
Pages: 1 2
Prev: learning access
Next: I have chopped head off picture.How do I restore orginal?