create query

[eugz]

I have table that looks like:
Case_Name.........Time.............ItemID.....Label.............RecNo
===================================================
35316E05.10G.....6:46:45 AM.....4...........Last Name......SHARIF
35316E05.10G.....6:46:45 AM.....5...........First Name......YANIS
35316E05.10G.....6:47:00 AM.....1...........Record #........262-50-45
35316E01.15G.....4:46:15 AM.....4...........Last Name.......DOU
35316E01.15G.....4:46:15 AM.....5...........First Name......JOHN
35316E01.15G.....4:47:01 AM.....1...........Record #........232-21-25

How to create query that result of data will displaying like one record? I mean like this:

Case_Name........Time...............Last Name......First Name.....Record No
======================================================
35316E05.10G.....6:46:45 AM.....SHARIF..........YANIS..........262-50-45
35316E01.15G.....4:46:15 AM.....DOU..............JOHN...........232-21-25

Thanks.

 

[CJ_London]

I would use a crosstab query if you just want to display the data rather than regularise it. I'm assuming the .... are not actually there and have just been used as spacers. In future, to preserve spacing, surround the data with code tags which you can find if you use the advanced editor.

In the query builder change type to Crosstab and set case name and time to row headings, label to column heading and recno to value. In the total row of the builder, set row and column heading columns to GroupBy and the value column to First

Because you have different times for the same case name you will need to use a formula to standardise on the time - suggest you use

RepTime: Dlookup("[Time]","YourTbl","[Case_Name] = '" & [Case_Name] & "' AND [ITEMID] = 4")

Note that Time and Label are reserved words so their use can have unpredicatable results and I recommend you change them in your table to say RTime and and RLabel.

 

[HiTechCoach]

Unfortunately a crosstab query will not work in this case.

TIP: Avoid Domain Functions like DLookup in queries. That get very slow.

I can think of two methods using only queries:

1) multiple queries b- one query for each record type. A master query that uses all the other queries joined on Case_Name

2) Subqueries (similar to Dlookup but must more efficient). See: Subquery basics

 

[CJ_London]

Unfortunately a crosstab query will not work in this case.

Why not? I've used it this way in the past when I have received similar data. If you are going to say something won't work it would be appreciated if you explain why.

TIP: Avoid Domain Functions like DLookup in queries. That get very slow

.

Agreed, but depends on data volumes as to whether there is a material effect. I must admit I tend to use sub queries but within the context of the forum the domain functions can be explained for use in forms and can be used in update queries etc.

 

[jdraw]

It looks to me that the "table" is really a report from some other process.
The ItemID (4,5,1) make these look like independent records.
If the order is the same throughout the "table", and 3 records relate to 1 individual,
I think I would use a recordset and loop through it.


Basic approach...
do while not rsIN.eof
Read 3 records, manipulate the fields, then write the new record on rsOUT
loop