[AccessD] Avoiding Duplicates on a Report

Gary Kjos garykjos at gmail.com
Thu May 19 11:04:03 CDT 2005


Concatination would mean putting two or more fields together into one
in a query something like
ProductIDs:[Product1]+[Product2]+[Product3]+[Product4]+[Product5]+[Product6]
would then allow you to select with a Like "*XYZ123*" in the criteria
line of the field. Then you could group by customer and NOT by product
to get the list of customers that bought the selected product.

Another way to do it could be to make 6 sub queries each selecting the
cust and one of the product fields. Then make a union query taking the
SQL for each of the individual sub-queries and put them together so
you now have a result set of customer ID and Product ID. Then do your
selection and group by cust and item in another query that uses the
union query as it's input.

Sounds trickier than it is once you do it. Union queries have to be
entered as SQL is the only stumbling block. Making individual queries
for each part you intend to union and then copying and pasting the SQL
from them into a union is the easiest way to build one.

Your example is a textbook one of why normalization is the almost
always the right way to go in database design.

Good luck with it,

Gary

On 5/19/05, Gowey Mike W <Mike.W.Gowey at doc.state.or.us> wrote:
> 
> Thanks everyone for the advice, I will certainly look at normalization.
> But for now how do I do about concatenated??  I have not heard of this.
> 
> Thanks
> 
> -----Original Message-----
> From: Darsant Silverstring [mailto:darsant at gmail.com]
> Sent: Thursday, May 19, 2005 8:41 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Avoiding Duplicates on a Report
> 
> On 5/19/05, Gowey Mike W <Mike.W.Gowey at doc.state.or.us> wrote:
> >
> > What do you mean by normalized?  The customer has the ability to
> > purchase up to 6 products on one invoice.  The table assigns the
> > invoice number and than records the products selected in the fields
> > Product1 thru Product6.  The customer# is pulled from the customer
> table.
> >
> > Mike Gowey  MCDST, A+, LME, NET+
> > Team Leader - East Region
> > Information Systems Unit
> >
> 
> In a normalized database, if the customer purcahses 6 products, there
> would be a sub-table such as InvoiceDetails, where you would have the
> InvoiceID and then a seperate record for each product purchased.
> 
> One of the problems with doing it as a field on the invoice is you end
> up with hard limits (using a subtable, the user could in theory,
> purchase as many or as few products as they want). There's many other
> reasons for normalization but I think that's left for another day.
> 
> However, to the problem at hand, since the data is not normalized best
> bet would be to fake it and concatenated the fields as suggested
> earlier.
> --
> Darsant Silverstring
> 
> "Peace cannot be kept by force. It can only be achieved by
> understanding."
> -Albert Einstein
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
Gary Kjos
garykjos at gmail.com



More information about the AccessD mailing list