Merging multiple records into one long record...

[Netopia]

Greetings,

I want to say up front that I'm NOT much more than a total neophyte at Access. If
I ask a "stupid" question here, it's because I really don't know that much about how to do queries.

Anyway, here is the situation. I've been given a database that has the normal sort of name and address fields, but it also has three fields that contain information about a purchased product.... the description of the product in one field, the product number of the product in another field, and the date the product was purchased in a third field.

What I've been asked to do is to take multiple records that have the same name and address but different products and associated data, and combine them into a single record with the three product records for each product purchased appended to the end of the record in chronological order of when they were purchased. I believe that no name/address combo has more than 5 products purchased.

How would I go about this? Is there an easy way with a single query, or would I have to do multiple queries and then merge the output somehow? Can it even be accomplished without knowing and using any Visual Basic?

I really am a noob, so no explanation can be worded to simply.

Thanks to all for any ideas you might have on this... I really appreciate it.

Joe

 

[KLin]

Do you have 2 separate tables in this database? One that shows names/addresses(customer data) and a table that shows purchasing information? This could be done with a query if there's a relationship setup between the 2 tables(a customerID related field in the purchasing table to show which products were purchased by which customers).

 

[Netopia]

Nope. Whoever originally setup this database (which is sent to us from a customer) simply put all the fields in one table, but with redundant name/address entries and unique product/purchase info. It seems like putting it all in one record should be easy, but it has certainly seemed baffling! (at least to me)

Joe

 

[Crusty]

You don't want to put it all in one table. The best way to do it is to have 2 tables with a foreign key relations between them. One table to hold the customer info(name/address etc etc) and a random assigned id(auto increment field). One table to hold the order information as well as the customer_id in a field where customer_id is the id of the customer that owns the order.

"SELECT * FROM customers INNER JOIN orders ON customers.id = orders.customer_id" -- That will return all orders for all customers.

You can then use the WHERE clause to specify what orders/customers you want. Something like "SELECT * FROM customers INNER JOIN orders ON customers.id = orders.customer_id WHERE customer.id = 1" will return only that customers order etc etc.

 

[JACKDRUID]

crusty is correct. your database is messed up.

however, if you are only looking for a quick report

1. create a tublar report from report wizard template
2. in record source, select name, address, product from yourtable group by name,address,product order by name
2. set 'hide duplicate" to yes

 

[KLin]

Originally posted by: Netopia
Nope. Whoever originally setup this database (which is sent to us from a customer) simply put all the fields in one table, but with redundant name/address entries and unique product/purchase info. It seems like putting it all in one record should be easy, but it has certainly seemed baffling! (at least to me)

Joe

What's the reasoning behind just showing this data in a single record?

 

[Netopia]

To Crusty and JackDruid:

Yeah... the db is set up in a rather crummy manner, but it is what it is, as that is what our customer has given us and they aren't about to go through tens of thousands of records to do the right thing at this point.

Originally, we were told that there would be only two records (and thus two products and product info) for each name/address. We were able to make that work by doing a query that created a record with the earlier series of product info first and the other last. But with four or five products, it's no longer a matter of first or last and we aren't sure what to do.

To KLin:

The resulting table will be given to another group who is producing variable data sales letters and histories to the customers of our customer. There will be one letter to each customer (though there are several instances of each customer in the original database).

The form that will have the info dropped into it is designed to have the items listed in chronological order. As such, we need to give a database to the people who program the variable data that has chronological consistency from field to field (e.g. first field is always the oldest and successive fields contain chonologically newer and newer data). That way, there is a single record for each letter they will produce, and that record will contain all the sales history info (or at least the last four or five items) for that customer.

This is going to be a repetitive job if we can keep it. Probably at least once a month... but we're struggling on how we can actually do it.

Joe

 

[KLin]

So the letter will contain the sales history info? You don't need to have the data in a single record. You can make an access report that would create a letter for each customer(grouping on the customer info), and it could show their sales history in the detail of the report. It's all about how you group the data in the report.

 

[Netopia]

The letters are created in a separate piece of software that is specific to the purpose. It creates .vdx files that are very small files that contain a .pfd of the static document(s), the data, and all the logic for selecting which of several documents (depending on demographics of the recipient) will be used. This small file is then pushed to a Sun workstation, where it is rendered into the final form and pushed to huge (monochrome or color) digital printers that are the lenght of a mini-van.

That said... if it's easy to create a report such as what you've outlined, why is it so difficult (or at least seems difficult to me) to concatenate the info in chrono order to the right?

Joe