Concatenate

[teagles]

Hi All,

I have 2 gig of order data which has duplicates due to different invoice dates. How can I combine the dates into one cell so I only had one row of Order data.

i.e. order number, price, order date, invoice date
same order number, same price, same order date, different invoice date.

How do I combine the second line with the first so that I only have the data once.

 

[DavidAtWork]

Do you need to retain both invoice dates or are you looking to retain only one, ie the oldest or the most recent. This will determine how you proceed

David

 

[teagles]

Yes unfortunately I need to keep the invoice dates

 

[jdraw]

Tell us more about your situation. Your business, your data, your database, your database skill level...
2 gigs of data is a lot of data. How do you know the size?
Have you done compacts and repairs on your database?

Where do Customers, Orders, Products etc fit in your database?

 

[teagles]

I know the size by looking at the size of the database really. As mentioned in my first post I have order numbers that repeat the reason for this is because the invoice number is different. I want to combine them so the order numbers only appear once

 

[jdraw]

Have you ever done a Compact and repair?

Access does not do automatic garbage collection. Every time you test something, create a new query/report/form you use up space. You won't recover that until you do a compact and repair.

Do your business processes include both Orders and Invoices?
Perhaps it's terminology, but an Order is an Order and may by paid/billed in multiple Invoices. Some of those Invoices could be for different Customers.
If you don't use some records on a regular basis, you may wish to archive them to other storage but I don't understand the business rationale for collapsing records.

 

[teagles]

Of course I have done a compact and repair. It is so large because it is 3 years worth of data and I have used a union query. The data I have I need thus the reason why it is there. The invoice date has changed due to split shipments. We are invoiced for stock received. The problem is when performing cost analysis whilest including the invoice dates total cost is repeated thus the need to combine the invoice dates into one column and showing order number and total cost once.

 

[DavidAtWork]

What's the maximum number of instances for the same order number, ie how many different invoice dates do you need to store
David

 

[Brianwarnock]

I've never worked on this kinda DB but it would appear to me that we have a simple one to many relationship between orders and invoices, shouldn't they be in separate tables?

Just ignore me if I'm talking rubbish as I'm sure you will.

Brian

 

[whisp]

The only ways i see are either using a string instead a date field and concacenate the dates into that string (short text) field or using a second table which links the primary key of your invoice table with an arbitrary amount of dates. After changing/creating the tables, both could be done relatively easy with VBA, if you're familiar with it. I'm not a specialist, so maybe someone else knows better ways.

 

[Pat Hartman]

First of all, tables have columns. Spreadsheets have cells and the two are very different. Columns should be atomic. That means they should contain only a single piece of data. You will have nothing but trouble if you try to mush data this way.

If you are running up against the maximum database size, you may be able to do some clean up to reduce the size.
1. Examine all the tables and remove any extraneous indexes. Keep in mind that Access defines hidden indexes for all foreign keys so you don't also need to define them.

2. Examine your schema and make sure it is properly normalized. You may have redundant data.

If those two steps don't free up enough space, consider archiving old data. And finally, the last option is to upsize to SQL Server.