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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Getting duplicate values when running query
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 InvoiceNumberprimary key Purchase Date Company Codecurrency code Invoice Products Purchased Table SKUprimary key Expiration Dateprimary key Qty Total Rate InvoiceNumberprimary key Projects Table Project IDPrimary key Project Description InvoiceNumber CustomerID Project Status |
#2
|
|||
|
|||
Getting duplicate values when running query
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" wrote: 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 InvoiceNumberprimary key Purchase Date Company Codecurrency code Invoice Products Purchased Table SKUprimary key Expiration Dateprimary key Qty Total Rate InvoiceNumberprimary key Projects Table Project IDPrimary key Project Description InvoiceNumber CustomerID Project Status |
#3
|
|||
|
|||
Getting duplicate values when running query
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" wrote: 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" wrote: 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 InvoiceNumberprimary key Purchase Date Company Codecurrency code Invoice Products Purchased Table SKUprimary key Expiration Dateprimary key Qty Total Rate InvoiceNumberprimary key Projects Table Project IDPrimary key Project Description InvoiceNumber CustomerID Project Status |
#4
|
|||
|
|||
Getting duplicate values when running query
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 |
Thread Tools | |
Display Modes | |
|
|