Jim,
I don't quite understand about creating the table. Do I have the queries make-tables? But how do combine all tables? I've tried to do append tables with the queries which kinda' works, but it doesn't distinguish between the queries. Ultimatly, I want my outcome to look like this format:
# Number of items #####
$ Amount of items $$$$$$
$ Amount Commission $$$$$$
When I export to excel, I get this outcome:
# Number of items $ Amount of items $ Amount Commission
###### $$$$$$ $$$$$
I don't want the second format because there are about seven items that go with different criteria (for example: New Business, Renewals, Cancels, etc).
I'm not quite sure what you're meaning with those hashes and dollar signs and
stuff let me give you an pointer on how to distinguish first.. You said your fieldnames for all he queries were the same and datatype?. To that extent if you create a table first and foremost....... (yes I know you can make the query create the table through 'make table query' .......but lets forget that for a moment, do it manually till you see what I mean....then you can progress to the make table query next when you have your head round my meaning here.
The point in creating the table is to create an empty bucket if you like in which to pour the data. You know that each of your queries will output the data in a fixed format whether thats text numeric whatever so the idea is to make a table that you know will hold the format of that data. Now you say there is no distinguishing feature I agree with you there there probably isnt at the moment
BUT you know what your each of your queries does and the meaning of them.
The trick then is to make ANOTHER column in
EACH of your query as a false column if you like a 'virtual data' column if you will you can achieve this simply in the grid by entering a value for the fieldname like this
Distinguisher: "This is query one data"
Now if we were to create that as a 'virtual' column in the query what is the next effect ? it is this.... you get a column of data with "This is query one data" in each and every row cell so to speak for the entire dataset......
that is the distinquishing feature....you do that for each of your queries so... if its four queries then its
Distinguisher: "This is query one data"
Distinguisher: "This is query two data"
Distinguisher: "This is query three data"
Distinguisher: "This is query three data"
Now in the
table in which you intend to dump this data you merely create an additional field called.........you got it... DISTINGUISHER and then using a simple append query if you like drag your query and dump all the query columns into the grid and Access will automatically recognise the query field headers as matching the table in which you intend to dump into INCLUDING your 'Distinguisher'. Hit the run button and there you have four datasets in the one table from which you can then distinquish what data is from what query purely by reference to the extra column in the physical table
I hope I am making sense?
As for the formatting thing without me fully understanding what you mean by it I can't advise yet I am sort of believing that all your columns are of data are the same datatype?. They have to be or this method is not going to answer your problem.
This table creation method multiple append is much the same as a UNION query except that we are placing the data in a physical table as opposed to JOINIING 'in the air' via a query so to speak
Hope this helps to a point if not come back
Jim
2
