I am facing a tricky little issue.
I have run a queary that returns the following info (as an example)
Session, topic, case, exam
1, topic1, (blank), Yes
1, topic 2, Yes, (blank)
I want to merge the 2 rows of the result into one row if the session number is the same. The result would be:
Session, topic, topic, case, exam
1, topic1, topic2, Yes, Yes
Any ideas on how to do this (if the question makes sense)?
Maybe I am just thinking this is hard, and am overlooking a simple answer.
Thanks
What you should really be asking for is SQL gurus.
To merge them by sessions you put
GROUP BY Session somewhere in your query.
If you are using Access’s visual thingy for creating the SQL click on the ‘sql’ thing to get manual sql and put the group by line in. you probably need more, or different. I am not an SQL expert, I just know that group by is what you need.
I suppose you could create a summary query with the fields:
Group by Session
First of Topic
Last of Topic
Min of Case
Min of Exam
This would only work for two Topics per Session though. If there was one topic, it’d show up twice, and if there was more than two, the middle ones wouldn’t show up. And i’m not sure Case and Exam would work right with Min.
Or maybe you could do something with a crosstab, with Session as the row heading, Topic as the column heading, and either Case or Exam as the field data. The downside to this is you can only show Case or Exam, and it’s not in the format you want.
SELECT Session,topic,case,exam FROM yourdatabasename GROUP BY Session
should be it. BIM I have done that off the top of my head and have no access to access (no pun intended) or SQL at the mo.
(BIM - Bear In Mind)
That won’t work. All fields must either have an aggregate function or be in the Group By clause. If you made them all Group By, the query would return the same as if there wasn’t any Group By’s. My example uses the First, Last, and Min aggregate functions.
Also, after FROM should be the table/query name, not the database name.
If you expect no more than two instances of a given session in your table, and the ‘yes’ entries will appear only in one copy, you need something like this:
SELECT table1.session, table1.topic, table2.topic, table1.case & table2.case AS combinedcase, table1.exam & table2.exam AS combinedexam FROM table AS table1 OUTER JOIN table AS table2 ON table1.session = table2.session
If you’re using the Access query builder, you need to add your root table twice, add a join between the two copies on the session field, and double click the join to select the ‘include all records from table1 and only related records from table2’ option.
If the ‘yes’ entries might be duplicated, or they’re bit fields (Yes/No in the Access table format column), it’ll be slightly different, but the same basic principle will apply. If there may be more than two rows for a session, you’ll need more than two copies of the table in the query.
Bear in mind that, for large tables, these queries can run appallingly slow. I don’t know any faster way to do it, though. It’ll help if you index the ‘session’ field.
And 12 hours later, I realized what I forgot. At the end of that SQL statement, ‘WHERE table2.key <> table1.key’, where ‘key’ is the primary key of the table. (What do you mean, the table doesn’t have a primary key? Add an autonumber field, then, for heaven’s sake!) Otherwise, you’ll have one row per row rather than one row per session, the rows differing only by which topic comes first … Again, in the Access query builder, you’d make a column with a value of table2.key with ‘<> table1.key’ in the Criteria row.
Hey, Mullinator-did this handle your problem?