Return Multiple Rows from a Single Record

[Fowz]

I'm not sure that that title truly explains what I'm looking for.

For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows

1st Obs Start
1st Obs End
2nd Obs Start
2nd Obs End
3rd Obs Start
3rd Obs End.

I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:

ID......Obs Start.............Obs End........
1....[1st Obs Start].....[1st Obs End]....
1....[2nd Obs Start]....[2nd Obs End]....
2....[1st Obs Start].....[1st Obs End]....
2....[2nd Obs Start]....[2nd Obs End]....
2....[3rd Obs Start].....[3rd Obs End]....
3....[1st Obs Start].....[1st Obs End]....
4....[1st Obs Start].....[1st Obs End]....

etc.

I could then quickly count how many obs periods started within the desired month.

I'm fairly new to Access, and have only dabbled in SQL, and so I'm hoping it's something fairly straightforward?

 

[Uncle Gizmo]

What value ties these two together? (1st)?

1st Obs Start
1st Obs End

 

[Fowz]

They are distinct fields in a single table, and so the common tie between them would be the ID number (primary key) for the record.

Is that what you meant?

 

[vbaInet]

You're probably trying to do a Crosstab query??

http://office.microsoft.com/en-gb/a...ad-by-using-a-crosstab-query-HA010229577.aspx

 

[Fowz]

I could probably do a basic count in a crosstab query, however I've also been asked if I could show additional fields from each record as well, which would only work in a select query.

In essence, I'm looking for a way to combine 1st/2nd/3rd Start dates into a single Start dates column, and similar for End dates.

 

[vbaInet]

I could probably do a basic count in a crosstab query, however I've also been asked if I could show additional fields from each record as well, which would only work in a select query.

Isn't this a matter of creating a new query that's based on the crosstab query and include the other fields?

 

[Fowz]

I'm afraid I'm completely lost now.

How would I do this with a crosstab query?

To go back to my original post, say for example I had the following:

Fields-....ID.....1st Obs Start....1st Obs End.....2nd Obs Start....2nd Obs End.....3rd Obs Start....3rd Obs End
.............1..........01/01/14.........05/01/14.........26/01/14...........03/02/14.................................................
.............2..........03/01/14.........18/01/14.........20/01/14...........23/01/14...........25/02/14........27/02/14......
.............3..........05/02/14.........10/02/14.............................................................................................
.............4..........13/02/14.........14/02/14.............................................................................................

How could I design a query to show me the above as follows:

ID......Obs Start.............Obs End........
1......01/01/2014..........05/01/2014......
1......26/01/2014..........03/02/2014......
2......03/01/2014..........18/01/2014......
2......20/01/2014..........23/01/2014......
2......25/02/2014..........27/02/2014......
3......05/02/2014..........10/02/2014......
4......13/02/2014..........14/02/2014......

?

 

[vbaInet]

What relates the Obs Start 01/01/2014 to the Obs End 05/01/2014? How do you determine whether 05/01/2014 should be the Obs End of 01/01/2014?

The same question that Uncle Gizmo asked.

 

[Fowz]

Just the numbering of the field name, i.e. 1st, 2nd, or 3rd.

 

[vbaInet]

You actually have fields name [1st Obs Start], [1st Obs End], [2nd Obs Start]... etc?
How many of these fields do you have?

 

[Uncle Gizmo]

Just the numbering of the field name, i.e. 1st, 2nd, or 3rd.

You don't have an ID field? Autonumber, primary key field?

 

[Fowz]

The example table I gave in post #7 is an excerpt from my database table. There is an auto number ID field, and then the start and end date fields for 3 observation periods only.

 

[vbaInet]

Yes I can see that but it still doesn't answer my question about how many of these fields you have in total?

 

[Fowz]

Just those 6 fields capturing the 3 observation periods. No more.

There are around 50 or so additional fields holding other information in the database, but nothing else relevant to the observation periods..

 

[vbaInet]

I'm sure those fields could be further normalised but I digress.

What you need to do is:
1. To create Obs Start combine all three Start fields:

Obs Start: [[COLOR="Blue"]1st Obs Start[/COLOR]] + [[COLOR="blue"]2nd Obs Start][/COLOR] + [[COLOR="blue"]3rd Obs Start[/COLOR]]

2. Repeat the same for Obs End and you're done!

 

[vbaInet]

On second thoughts this may not be what you're after. It's hard to visualise a proper solution when the fields aren't properly normalised.

Upload a sample db (with some test data) and I'll look into it.