Report to Show Two rows for Each Record

[khwaja]

I have attached a sample spreadsheet. Each store has a planned date which is calculated off the dates in column b and c. The second row is actual date.

As much of the data I have is in MS Access, I tried to use some extracts to appear in this format but no luck. I am hoping may be I can use the report function to achieve this? Could someone give me some insight?

Cheers

 

[JHB]

Should the report be exactly as the spreadsheet, amount of controls/fields?
What data do you exactly have?
What did go wrong, when you tried to create the report, which problem(s)?

 

[khwaja]

Unfortunately yes, it should look like the spreadsheet.
I have a projects table where I have actual dates for all these columns (or as they become available) and I have created a query where I could generate planned dates using store opening date. So I do have the required data in two separate queries. But the issue is how to merge these into one and call them planned or actual. I tried union query but as i have to use same column names and numbers, it does not help much.

 

[GinaWhipp]

You can use a UNION query. Simply use Null as a placeholder. Only exception will be is if is the first query then you have to do Null As SomeName because you cannot have Null as a Column Header, i.e.

SELECT apAssociateID AS ID, Null AS Company, Associate AS Recipient, 
eMail
FROM qryAssociates
UNION SELECT dsDatabaseSupportID, dsCompanyName, dsName, Null
FROM tblDatabaseSupport;

 

[khwaja]

Thanks a lot for your kind help.

 

[GinaWhipp]

You're welcome!

 

[khwaja]

If I may just ask a supplementary question, how do you group values in a union query? The resulting query I have created, has state and store names repeating each alternate row as I have planned and actual dates.

 

[GinaWhipp]

Never even try to Group in a UNION query. Drop the UNION into a SELECT and do you Grouping and Sorting there. The Unions only purpose is to *marry* the data.

 

[khwaja]

Thanks. Understand but the problem is that in the select query, I have nothing to report in terms of aggregation. I just have dates. I am listing stores by state and store number and two rows of dates planned and actual. Summary option needs something to be summed or similar.

 

[GinaWhipp]

Hmm, two queries, one shows Actual the Other shows Planned. Make the first column in the UNION query show that...

SELECT Null As Actual, apAssociateID AS ID, Null AS Company, Associate AS Recipient, 
eMail
FROM qryAssociates
UNION SELECT "Planned", dsDatabaseSupportID, dsCompanyName, dsName, Null
FROM tblDatabaseSupport;

This will get you that part separated after you sort by Store. Hmm, you may also then need to create a CROSSTAB to get the results to look more like a spreadsheet.

 

[khwaja]

Thanks. Will try that. If no luck I will deal with it in MS Excel.

 

[GinaWhipp]

Or you could come back and explain your Table structure and maybe we could work out another option.