Help with denormalizing data

[dawnd3]

I have a client that wants me to import their access data into another program. The only problem is, the other program will only accept a flat table import, like from Excel, where each Member on the list has just one record. It is really dumb but I have called the company and this is what I have to do to get the data in. So the data consists of members and their one or many donations. Basically I need to have one row with the member info and columns for donation 1, donation 1 date, donation 1 amount, donation 2, and so on. I know I can just pull it into Excel and do it manually, but I was checking with you folks to see if there might be a function or macro or something that can do this for me automatically. Or if you have ideas on how I can do it via a query. I am not a high level programmer, so please give me detailed explainations. THANK YOU!

http://www.calliopeconsulting.net

 

[fneily]

This has been answered a number of times. You can search
Microsoft: Access Queries and JET SQL or all of the Access forums on "concatenating", concatenation, etc.
I was going to get some threads for you but right now, Sunday night, the tek-tip search engine is down.
Also look at:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

See the hyperlink:
Generic Function To Concatenate Child Records (46 KB) Access 2000

 

[lespaul]

Try FAQ701-4233



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[BobRodes]

By the way, "denormalization" as it is generally used is a valid technique to improve performance, for example placing calculated fields in a table to avoid doing the calculations on the fly. In order to distinguish this technique from what they're asking you, perhaps we could use the term "un-normalization", which would be a generally invalid technique of reorganizing the data so they are not even in first normal form.



Bob

 

[MichaelRed]

UGH!!!




MichaelRed

 

[BobRodes]

Well, then again, perhaps not....

 

[dawnd3]

Bob, thanks for the tip. I was kind of wondering what the proper term to use was.

fneily and Leslie, thank you for pointing me to the links, but I am not sure if that is exactly what I am looking for. I don't want to concatenate the child fields into one field, I want to have each value in a separate column. Basically repeating columns of data. For how ever many donations, there would be fields for each, "Donation1", "donationDate1", "donation2", "donationDate2" and so on. If I am missing something and the above solutions will work for me, please let me know. Thank you all for your help.

Dawn

http://www.calliopeconsulting.net

 

[rac2]

Is there a way in Access to export the results of a PIVOT query. If so, and you could produce a pivot table with one row per donor and one column per donation sequence number, then you might have it.

 

[BobRodes]

<I was kind of wondering what the proper term to use was.

I don't think there is one, seriously; it seems unlikely that there's a proper term for improper technique. Perhaps "enkludge" is a better term than "un-normalize." What do you think, Ted?

 

[dhookom]

If you want to create virtual numbered columns you can use a crosstab query. For instance if you wanted to number and display OrderDates by CustomerID in the Northwind Orders table, you would use SQL like:

TRANSFORM First(Orders.OrderDate) AS FirstOfOrderDate
SELECT Orders.CustomerID
FROM Orders
GROUP BY Orders.CustomerID
ORDER BY Orders.CustomerID
PIVOT "Order" & DCount("OrderID","Orders","CustomerID =""" & [CustomerID] & """ AND OrderDate<=#" & [OrderDate] & "#");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dawnd3]

Well I got it to work! I used Duane's example (posted by fneily) and I figured out how to make it work. I basically concatenated all 4 donation fields(altering Duane's sample) and then after running the query I brought it into Excel and separated it using Text To Columns. Looks perfect!

Thank you everyone!

Dawm

http://www.calliopeconsulting.net