Two table query - repeating customers please advise

[Gary]

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
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.

 

[Guest]

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
your customers details with a subform of orders, related by customerID.

HTH

Regards

 

[Gary]

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
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.

 

[Guest]

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

 

[Guest]

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