Getting duplicate values when running query

[larochy]

Hi, I've created a database to track invoices sold, products purchased on
each invoice, and then projects that are initiated against the invoice which
is basically a bucket of consulting hours the customer bought from us for
services my company provides...ie. Application Engineers, Consultants,
Project Mgrs, etc. An invoice can have multiple projects attached to it and
I'm running into a problem when I want to run a query showing the invoices,
products purchased for each invoice, and the usage at the project level
against the invoice balance. I'm getting duplicates, see below for my table
structure. I'm sure I'm breaking some "normalization" rules in my tables but
I can't seem to get this to work. Any suggestions?


Invoice Table
Cust ID
InvoiceNumber<primary key>
Purchase Date
Company Code<currency code>

Invoice Products Purchased Table
SKU<primary key>
Expiration Date<primary key>
Qty
Total
Rate
InvoiceNumber<primary key>

Projects Table
Project ID<Primary key>
Project Description
InvoiceNumber
CustomerID
Project Status

 

[Fred]

If I may start with an answer to the question implied in the title and then a
comment related to that.

A query which show fields from multiple joined tables will have a line
("row") for each instances of a set of joined records that meets the
criteria. And so it would be expected (= not a malfunction)) to have lines
where much (but not all) of the data looks like duplicates. 99% of the time
when you want to see, in one view, data from multiple tables you need to use
a form, not a query.

You need to clarify to yourself (and then to us) the exact nature of the
process/information that you are trying to database. You probalby should
and can make data rules that avoid "many-to-many" relationships. For
example, while there may be many different order items related to a project,
you probably should make a rule to break up invoice line items so that there
is never more than one project related to one of them.

"Invoice" is one of the tricker terms to define because it has fundamentally
different definitions in most developed Access applications (e.g. Northwind,
where it is in essence a report which has header information plus line items
with prices and a summary) vs. enterprise softwares which treat it as a one
time event/transaction. Let's assume it's the former.

You'll also have to precisely define what you mean by " usage at the project
level
against the invoice balance"

Takinng a guess at the answer to all of the above, I don't see any breaking
of normalizaiton rules in your structure, but it is probably not the best one
to reflect your process (as I guess it to be) probably need these tables:
(of course, you can shorten my descriptive names)


tblCustomers
tblOrders
tblOrderItems
tblProjects



Sincerely,

Fred

 

[larochy]

Thanks for the response Fred. My company sells enterprise software. Along
with the software, we also sell consulting services upfront. The consulting
services are sold on the same invoice as the software and the services are
defined by certain SKU's, ie. implementation services, training, general
consulting. My job is to take the services piece and recognize the "deferred
revenue" created by selling the services upfront and then having to track the
usage(hours) against this balance that the customer bought. Sounds like the
way they set it up though inherintely creates duplicate "rows" or instances
when they want to have more than one project possibly go up against one line
item from the original invoice. I created a query to consolidate the
services sold on one invoice down to a single line item with a total qty of
hours sold and a total $ amount. When I try to run/create a report showing
this one line item up against multiple projects attached to this one
consolidated line item, iit seems to multiply the "total" amount by the
number of projects I have attached to it and overstates the original balance.


It sounds though as if we just have a lot of "many-to-many" relationships
and I know that's not alwaysa good thing in a database. Sounds like you're
saying the best way to show this though would be on a form with the invoice
data on top with a subform below showing the usage by project, is that
correct? Can I generate a report against this form?

And I do basically have the exact tables you reference below. A customer
table, Order/Invoice table, Order/Invoice detail table, and then a Project
table that shows the consulting hours or usage against the original invoice.

 

[Fred]

Dear Larochy,

So I guess you are using enterprise software to the main transactions and
are capturing some "services" line items from the invoice for a seperate
database which tracks provisions of those services against the sale.

Two ways we could go from here...either

1. Me ask many more questions to get the needed solid foundation to
recommend a solid solution. I'd be happy to do that if you want to go
though more writing. I suspect that your main work will be on steps #1 &
#2 (below) and that doing that will make step 3 easy.

2. Make a few comments based on a "gut feeL and things that you've said.
Here goes on that: (please excuse the directness)

Overall Step one is defining / organizing what your data is and what you
want to do with it.
Overall Step two is designg a table structure based on step 1
Overall Step three is the forms and report to accomplish your objectives,
plus any queries needed to make the forms and reports work.

You're probably not done with steps 1 & 2.


Gut feel is that, somewhere in your system, you'll probably need to be
recording individual instances of your folks providing those services that
the customers bought. I don't know what "projects" are, but I assume that
they relate to this.

Queries should designed for the specific report or form that they support.

With regards to DISPLAY of data, a report and a form do basically the same
thing, except one's on-screen, one's on paper. so once your have a working
form, you can easily create a report that does the same thing, but you would
you would not "generate a report against a form",

Hope that helps a little.

Sincerely,

Fred