Output and Formating - combining multipule outputs into one

[Limbofrog]

Ok - I have three queries that sort one set of data different ways.

Query1 - sorts by date for "this week" only
Query2 - sorts by date for "next week" and greater
Query3 - sorts by all other data without a date in order of client number.

I have a main form to edit this data that shows all three queries as subforms in a datasheet format. If it helps this is job data and my company has a job meeting every week on monday. Our joblist is orginized as the quries show above. That way we can see whats due this week, next week, and then after that and in that order.

I coded an expresion on the main form to export all three queries as text files upon closing the main form. It works great. I have currently linked an excel file to these three files - one in each "worksheet".


Ok, so I now I have three seperate comma delimited text files that have the correct data and it's in the right order. I just need to combine them. I can use excel or access, I don't care which - But I want to combine them and format it to my pleasing(putting formating lines between each section and making the font sizes right for each section.

Do I need to combine them upon exporting to the text file? or is there a good way to do this in excel after the fact. I was thinking of making it an excel file so everyone in the office can open and view it.

Any ideas?

 

[jmersing]

Re: Output and Formating - combining multipule outputs into

When you say combine them, I'm not sure what you mean. View them as if you were looking at your form? So all three query results can be seen on the same sheet? If so why not include them all on an access report by using sub-reports, you can add multiple query results to a report just the same as a form.

You could also then export the report into Excel word, snapshot etc. Your choice.

Does that help?

 

[Limbofrog]

Re: Output and Formating - combining multipule outputs into

Well, that gives me some good ideas.

But what I meant when I said combine them was this:

And first let me add - all three queries and/or export text files have the same fields and in the same order so they all match up. I wanted to take each query and combine it into a single table. Stack them if you will all on the same excel sheet. Rows 1-8 might be the data from Query1, rows 9-15 might be Query2, and rows 16-28 might be data from Query3. The problem is I never know how long or how many rows the query will have that week. It depends on how many jobs we finish and how many new jobs we get. Regaurdless of thier length, I want to combine them one after the other into a single table.

I don't care if I do it in Access or Excel or during export. I just wondered what might be the best and/or easiest method to do this. I have not tried sub reports yet, I will read about that first thing in the morning and see if that works. If you have anything else to add, I am open to ideas.

Thanks

 

[CT Witter]

Re: Output and Formating - combining multipule outputs into

If you want to combine them, you may also want to use a union query. This will take the data from the three queries and combine into 1 unified query.

I guess you could also run the first query as a make-table query, and run the other 2 as append queries to get all the data in one table for export.

If you want to do special formatting for each section, I second the notion of using sub-reports. If you set the can grow/shrink properties correctly, the report will resize automatically to accomodate addition or deletion of rows.

HTH,
CT

 

[Limbofrog]

Re: Output and Formating - combining multipule outputs into

Ok, you guys are helping tons

I got the subreport working for the most part. I just create a main report in design view with the correct headers. Then I put in a subreport for each of the three queries. So I have three subreports in the main report. When I display or preview it, it looks good but it is duplicating many pages. It seems that maybe it's making a duplicate report for every entry in the first report. So basically the first three pages are correct and complete as I like them. But then they repeat 9 times I think it was. All I have to do is print the first three pages I want but it's frustrating that I can't figure out why? Any ideas?

Also I was playing with the Union Code. Where would be a good place for this code? maybe as a VB "event proceduce" on a command button on the form that this report comes from?

 

[CT Witter]

Re: Output and Formating - combining multipule outputs into

Without actually seeing the database it is hard to provide you with an accurate answer. It could be formatting, but is probably related to how your report is setup and whether or not Access thinks that the sub-reports are linked.

If you want to use the union query...Create a new query and you will have to select SQL Specific-> Union. You will have to type in SQL commands to make this work.


Then you would refer to this query in your report, just like any other query or table.

CT

 

[mdmilner]

Re: Output and Formating - combining multipule outputs into

I think I'd go back to CT's first suggestion. Make-Table + 2 Queries before doing subreports. Depends on what you need to see.

A different thought is this.
Create a query that adds a calculated field that populates with the values of 1/2/3 (as an example) depending on the conditions you specified in each of your original queries. If it's this week, it's a 1, next week 2, and everything else without a date 3.

Next, Create a report based on this query with the wizard interface and GROUP by the different numbers calculated above. This will break up the report vertically. If you'd like to be a little slicker, populate the fields with some descriptive text value (Current/Next/None) etc etc.

Now, if you don't need a report, just make sure you set sort the query and export it directly to Excel if that's what you need.

Mike

 

[Limbofrog]

Re: Output and Formating - combining multipule outputs into

If you want to use the union query...Create a new query and you will have to select SQL Specific-> Union. You will have to type in SQL commands to make this work.
CT

I created a new query. The choices were to use:
(1) Design View
(2) Using Wizard

If you pick Design view it asks for which Tables or Queries to use as a reference. I picked the three queries that have talked about above. Then its just a blank Design Query sitting there waiting for me to input. Where so you "select SQL Specific ->Union" as you put it? I see that option nowhere.

I do see Append Query and Delete Query and Select Query.

Err - feeling frustrated. Sorry if I ask to many questions... still learning lots.

 

[Limbofrog]

Re: Output and Formating - combining multipule outputs into

Thanks to a post by mdmilner I found this website and found an answer with a great example for anyone else who might read this and need it laid out for you like me =)

http://www.fabalou.com/Access/Queries/unionquery.asp

I love the net... hard to remember life without it.

 

[Limbofrog]

Re: Output and Formating - combining multipule outputs into

Ok, I am really getting somewhere now. I did not see that the Query/SQL/Union Query was an option on the Query Menu at the top of Access.

Now, here is my code for the Union Query and it works... but with one problem. It scrambles the order back up different that what the three queries have.

"
SELECT [Client#],[Job#],[ClientName],[ProjectName],[Drawer],[PhaseSpecs],[Due],[ProjectMgr],[MechMgr],[ElecMgr],[FeeBasis],[ConstructionDocsPer],[ConstructionPer],[JoblistNotes]
FROM [JobList_CurrentProjectsThisWeek]

UNION ALL SELECT [Client#],[Job#],[ClientName],[ProjectName],[Drawer],[PhaseSpecs],[Due],[ProjectMgr],[MechMgr],[ElecMgr],[FeeBasis],[ConstructionDocsPer],[ConstructionPer],[JoblistNotes]
FROM [JobList_CurrentProjectNextWeek]

UNION ALL SELECT [Client#],[Job#],[ClientName],[ProjectName],[Drawer],[PhaseSpecs],[Due],[ProjectMgr],[MechMgr],[ElecMgr],[FeeBasis],[ConstructionDocsPer],[ConstructionPer],[JoblistNotes]
FROM [JobList_CurrentProjectNoDate];
"

This code combine all three queries and it keeps each query seperated from the other. However like I said, within the query it is mixing up the order of the records.

I tried using the ORDER BY code but it only wants one order by statment for the entire union query. Meaning it applies it to the final product instead of each query as they come. Even if I use three ORDER BY staments, one after each SELECT statment, it seems to only use the last one or access gets confused.

The three queries were in the right order before I unioned them. That order was:

Query1 = JobList_CurrentProjectsThisWeek - ORDER BY [Due], [Job#]
Query2 = JobList_CurrentProjectNextWeek - ORDER BY [Due], [Job#]
Query3 = JobList_CurrentProjectNoDate - ORDER BY [Client], [Job#]


Any leads or points what I should try now? This reminds me back when I used to play "Falcon 3.0" on the old DOS 3Mhz PC and was tring to load everything into hign mem and get 620K free of memory so I could have sound for more than 10 seconds =)