Question Help with building a Query

[MellowHokie]

Still relatively new to Access 2010 and trying to develop a database to develop and store different components of data needed for my company. One problem I have run into is developing a query for the following data...


ID Delay Time (1) Delay Reason (1) Delay Time (2) Delay Reason (2) Delay Time (3) Delay Reason (3) Delay Time (4) Delay Reason (4)
1 0.25 PI 0.5 MB 0.25 FS 1 BD
2 0.25 PI 0.5 MB 0.25 FS 1.5 BD
3 0.25 PI 0.5 MB 0.25 FS 0
4 0.25 PI 0.5 MB 0.25 FS 2.5 TD
5 0.25 PI 0.5 MB 0.25 FS 1.5 BD
6 0.25 PI 0.5 MB 0.25 FS 1 BD
7 0.25 PI 0.5 MB 0.25 FS 1 BD
10 0.25 PI 0.5 MB 1.75 LD 0
11 0.25 PI 0.5 MB 2 UM 0.75 MC
12 0.25 PI 0.5 MB 0.5 LD 2.75 SM
13 0.25 PI 0.5 MB 0.25 LD 0
14 0.25 PI 0.5 MB 0.75 MC 0
15 0.25 PI 0.5 MB 3 BD 0.25 TD
16 0.25 PI 0.5 MB 0.25 LD 1 UM
21 0.25 PI 0.5 MB 0.25 FS 1.5 SM
22 0.25 PI 0.5 MB 0.25 FS 4 UM
23 0.25 PI 0.5 MB 0.25 FS 9 UM
24 0.25 PI 0.5 MB 0.25 FS 3 MC
25 0.25 PI 0.5 MB 0.25 FS 0
26 0.25 PI 0.5 MB 0.25 FS 1 LD
27 0.25 PI 0.5 MB 0.25 FS 1.5 BD
31 0.25 PI 0.5 MB 0.25 FS 1 TD
32 0.25 PI 0.5 MB 0.25 FS 0

Where the delay time is in hours and the delay reason is code for specific reasons that delay has occurred. How can I take the data show above and create a table that counts the occurrence of each of the 9 code types and then sums up the delay time associated with each delay reason... the output table I would like to make via Report should look like....


Reason Count Total Delayed Time
FS # #
PI # #
LD # #
BD # #
TD # #
MB # #
UM # #
SM # #
MC # #

Thanks for any assistance anyone can provide.

 

[plog]

First, with the spaces in your field names and no delimiting character, I have no idea what your sample data is telling me. I can't discern where one field's data ends and the other begins. Put some commas after every field name and data value.

Second and foremost, when you have numerated field names (i.e. item1, item2, item3, contact1, contact2, contact3, etc.) it means your database isn't properly structured. Instead of having a field for each item, you need to make a table of items with a row in that table for each item.

Every row of data you have given should instead be 4 rows in a new table (possibly called Delays). Then using an aggregate query you can easily sum up your data and get it into a query that you can base a report off of.

 

[MellowHokie]

Assuming these values come from a preexisting database which is being updated using a corresponding form, how can i create this "delay" table that has just 3 columns instead of all 9 without affecting the data in the original database or the ID counting from the original database. Thanks.

Apologies for the format of the first post, the format came from Excel after transferring my database information there.

 

[plog]

I would create the Delay table with the fields I described and then make 4 append queries to move the data from your source table to the Delay table.

In the first append query you would move the ID, Delay Time (1), and Delay Reason (1) into the Delay table. The second append query would move ID, Delay Time (2) and Delay Reason (2). The third and fourth would move their respective delay data.

If the number associated to the Delay Time and Delay Reason fields is important, be sure to move that piece of data to the Delay table as well.