Question Help on formulating sort and group by Query

[jonathanchye]

Hi all,

I have a table with the following structure :

ID-----Collection Date-----Department
1 -----12/10/2011----------IT
2------12/10/2011----------IT
3------15/10/2011---------IT
4-------12/10/2011---------Finance
5-------13/10/2011---------Finance
etc...

ID is the PK of the table.

What I want to do is group them first by Collection Date then by Department and give them a unique Order No. So I want something like this:

Order No.-------------Collection Date------------Deparment
ACME001------------12/10/2011----------------IT
ACME001-------------12/10/2011----------------IT
ACME002-------------12/10/2011-------------FINANCE
ACME003------------13/10/2011-------------FINANCE
ACME004-------------15/10/2011-------------IT

Is there someway to do this? I am thinking of a make table query but can't get it to work as I've set the Order No to be unique.

Any help/advice much appreciated

 

[MStef]

Put the "Order No" Unique = No.

 

[nanscombe]

In the query you could try making the column Order No from the Collection Date and the first 2 characters of the Department.

Something like Order No: Format([Collection Date],"YYYYMMDD") & "/" & Left$([Department], 2) which, when sorted, should give:

Order No.-------------Collection Date------------Deparment
20111012/FI-------------12/10/2011-------------FINANCE
20111012/IT------------12/10/2011----------------IT
20111012/IT-------------12/10/2011----------------IT
20111013/FI------------13/10/2011-------------FINANCE
20111015/IT-------------15/10/2011-------------IT

ETA:
I made the assumption that you want to group similar records together for reporting purposes.

However, if you are making a change to table structure then I would keep the Order No as the Primary Key and add another field as Order Code and use the above idea to populate it.

 

[vbaInet]

Why this exercise? Are you restructuring your tables?

 

[Lightwave]

I would set the ID in the first table to be unique...

Then you just need to a query to sort in the correct order and you have your list.

Alternatively add an OrderNo field to your first table set default value to autonumber and make it the primary key.

Putting letters in primary key fields generally is always a headache.

 

[jonathanchye]

Thanks for all the replies!

I am restructuring tables because I am importing data from a few Excel workbooks into one single database.

To clarify things below is my current DB table structure and relationship

Table : tblGroupedOrders (all grouped on same say to send to a common Courier)
PK : OrderID
orderDate (for imported data we will assume this to be the collection date as info missing from spreadsheet)
orderSentBy
Department

Table : tblOrderLines (each individual orders)
PK : OrderLineID
FK : FKOrderID
CollectionDate
CollectionTime
DeliveryDate
DeliveryTime
etc...

Those 2 are my main tables in the database

In the excel spreadsheets we have one big "tblOrderLines" after merging different departments. I will also need to generate a Order Note hence why I need to group them and assigned common Order No.

I'm thinking of first importing all into a big temp table and assign a unique code to all which combines deparment and a sequential number

I want to then first make a table by grouping from this temp table into Orderdates and then assign Order No by using a query (Dmax(ID)+1) but this is where I am stuck at... If I can get pass this stage I can then import it into tblGroupedOrders and create another query to import into the subtable tblOrderlines by matchin the unique code...

 

[nanscombe]

Ah, data migration.

I've knocked up an example of how I would handle this using just queries and a couple of temporary tables.

Tables:
tblImport - Table full of imported data. Aliased as 'I' in the queries.

tmp_DepartmentCollectionDate - Temporary table of unique CollectionDate, Department pair. I've added an autonumber field to automatically create an OrderCode for me. Aliased as 'DC' in the queries.

tmp_DepartmentCollectionDate (Duplicate) - A duplicate of the above table. You can use this to replace the other table to start at 1 again.

tmp_UniqueDepartmentCollectionDate - Temporary table containing up to date unique CollectionDate, Department pairs. Aliased as 'UDC' in the queries.


Queries:
qryUniqueDepartmentCollectonDate - A query to give me an ordered source of unique CollectionDate, Department pairs. Aliased as 'qry' in the queries.

zResetOrderCodesToZero - Resets all the OrderCode values, in tblImport, back to zero.

(As the song says .. You just follow steps 1, 2 & 3.)

qryStep001 - Make tmp_UniqueDepartmentCollectionDate - Creates an up to date copy of tmp_UniqueDepartmentCollectionDate.

qryStep002 - Add missing records to tmp_DepartmentCollectionDate - Adds any new records to tmp_DepartmentCollectionDate.

qryStep003 - Add OrderCode back to tblImport - Adds the new OrderCode to the tblImport records.


I hope this is useful.

 

[jonathanchye]

Hmm, managed to create a data macro that almost does the job but some didn't go through because of key violations.

I have a thought, is it possible to first remove the relationship between my tables, import all data in with the appropriate key references and then enable relationships after importing all in?

The complex thing is actually the "grouping" process on the the main table but if I can import all in and then re-enable relationships then this problem could be overcome... any thoughts?

edit: I just saw your reply Nanscombe. Thanks! I will have a proper read and have a go! This data migration gives me big headache at the moment. I've actually almost completed the new database but company wants me to migrate year's worth of data!

 

[nanscombe]

Data migration can be awful.

Migrating the data in the correct order can stop some of the problems, ie lookup data before main table data.

I usually import all the data into temporary tables in a separate Access database with a copy of the main data tables and no relationships .

Work out all the manipulations in them until the migration works correctly.

Only once I have run through the routines a few times, and am happy with the results, will I link to the live tables to do the migration proper.

 

[jonathanchye]

Data migration can be awful.

Migrating the data in the correct order can stop some of the problems, ie lookup data before main table data.

I usually import all the data into temporary tables in a separate Access database with a copy of the main data tables and no relationships .

Work out all the manipulations in them until the migration works correctly.

Only once I have run through the routines a few times, and am happy with the results, will I link to the live tables to do the migration proper.

Agree. I am just currently testing most things offline and to be honest the discrepency only came to light when I was building the reporting module. Some numbers are correct but some not

I've uploaded a very stripped down version of my table structure. I can't include anything else due to confidentiality reasons. Basically I clease and import all data into tblOrderLinesTEMPDepartment1, tblOrderLinesTEMPDepartment2 etc as a first step.

I then run a query to group only the collection date from the TEMP tables and Append it into tblOrderNo (our main table). I tried grouping other required fields but that messed up the grouping as the query selected everything.

I then run another import from the TEMP table into tblOrderLines (which is basically everything). The only catch now is that I need to link them manually as the Append query doesn't automatically create the links in my situaion. This would mean updating "FKOrderNo" in tblOrderlines.

I've tried various method but failed so far due to "key violations" error. The weird thing is that some went through ok but some didn't...

I know I am probably asking for too much now Nigel but I hope you could take a look at it when you have some spare time.

Cheers!

 

[jonathanchye]

Another approach I am thinking of trying is to delete all relationships and import data into tables. After the import completes and I have successfully (manually) link the Foreign Keys I think re-enable the relationships for further additions. Will this work?

 

[jonathanchye]

Interesting observation from your sample as well. I've accidently deleted tmp_DepartmentCollectionDate table so I just copied the Duplicate one and renamed it.

After doing so your queries will not work again due to key violations.

Also, I think I have accomplished so far what you have done here but I am glad that I am shown an alternative way of doing it If you have a look at my attachment its the sub-table that I am having problems with...

 

[jonathanchye]

OMG! I found out why I had the key violations. It is because my search query is returning NULL values even though I am 100% FK exists. I then looked into the formula and discovered in DLookup for dates it reverts to US format. So in my table 5th of January 2011 will end up as 1st of May 2011 in the Dlookup!

I've converted the dates and now its perfect

 

[nanscombe]

That's good news.

Dates can be a right royal PITA.