Two table query - repeating customers please advise

#1 (**permalink**) 28-Jul-2006, 08:41 AM

Hello,

Hopefully there is a simple solution to this.

I have two tables. One is customers, one is orders.
Both share the key Customer ID.

I have used the simple query wizard to create a query and selected
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13955-two-table-query-repeating-customers-advise.html
fields from both tables. However the table that is produced is creating
a new row for every order that a customer has placed. And thus
duplicating customer details on every order row. This is not what I
need.

I need one row per customer. Some customers have 90 orders, and order
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13955
details are about 9 fields, so I would like each customer row to have
about 900 columns, called ORDER1, ORDER2, ORDER3, etc...

Does this make sense? Please help if you can.

Thanks,
Gary.

#2 (**permalink**) 28-Jul-2006, 08:41 AM

Hi,

Its possible but will take some quite heavy programming, mainly because its
not the way querys work.

If you want to see orders related to customers, i would create a form for
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13955
your customers details with a subform of orders, related by customerID.

HTH

Regards

"Gary" wrote:

> Hello,
>
> Hopefully there is a simple solution to this.
>
> I have two tables. One is customers, one is orders.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13955
> Both share the key Customer ID.
>
> I have used the simple query wizard to create a query and selected
> fields from both tables. However the table that is produced is creating
> a new row for every order that a customer has placed. And thus
> duplicating customer details on every order row. This is not what I
> need.
>
> I need one row per customer. Some customers have 90 orders, and order
> details are about 9 fields, so I would like each customer row to have
> about 900 columns, called ORDER1, ORDER2, ORDER3, etc...
>
> Does this make sense? Please help if you can.
>
> Thanks,
> Gary.
>
>

#3 (**permalink**) 28-Jul-2006, 08:41 AM

Oh no this is bad news...

The problem is that I'm trying to 'export' the data in this form. I'm
importing into a CRM program, that treats every row as a unique contact
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13955
so I need only one customer per row.
The customer order data is only associated with a customer if it
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13955
appears on the same row as the customer in this CRM program's import
facility (it is Act v. 6.0)

Any suggestions please?

Thanks,

Gary.

#4 (**permalink**) 28-Jul-2006, 08:41 AM

OK,

I can under stand having one customer for per line, thats standard, but why
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13955
would act then need each order in a column. I'm not familiar with act, but
this seems an odd way of doing things.

Rico

"Gary" wrote:

> Oh no this is bad news...
>
> The problem is that I'm trying to 'export' the data in this form. I'm
> importing into a CRM program, that treats every row as a unique contact
> so I need only one customer per row.
> The customer order data is only associated with a customer if it
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13955
> appears on the same row as the customer in this CRM program's import
> facility (it is Act v. 6.0)
>
> Any suggestions please?
>
> Thanks,
>
> Gary.
>
>

#5 (**permalink**) 28-Jul-2006, 08:41 AM

I would say you need, check act. Any database type system worth its salt
would not work like this.

Sorry to advise on company matter, but why use another program for CRM, if
your data is in access, use access.

Rico

"rico" wrote:

> OK,
>
> I can under stand having one customer for per line, thats standard, but why
> would act then need each order in a column. I'm not familiar with act, but
> this seems an odd way of doing things.
>
> Rico
>
> "Gary" wrote:
>
> > Oh no this is bad news...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13955
> >
> > The problem is that I'm trying to 'export' the data in this form. I'm
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13955
> > importing into a CRM program, that treats every row as a unique contact
> > so I need only one customer per row.
> > The customer order data is only associated with a customer if it
> > appears on the same row as the customer in this CRM program's import
> > facility (it is Act v. 6.0)
> >
> > Any suggestions please?
> >
> > Thanks,
> >
> > Gary.
> >
> >