Getting values from multiple queries

[BadgerLikeSpeed]

Hi,
I have 10 different queries, all of which contain a field [StartDate], [EndDate] and [FuelUsed] - amongst others. They come from different tables that have been input by users in differing locations (hence the separate tables/queries).
What I need to do is get the data into this form:
[StartDate] [EndDate] [FuelUsed1] [FuelUsed2] etc.
However when I try to create a query using the wizard I get an error message saying 'You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table or query based on that table. If so, try choosing fields from only the table or only the query.'
How do I get around this problem? How do I define relationships between things? I'm a little bit basic at this, so please keep responses simple please
Any help would be greatly appreciated!

 

[PeterF]

I think you need a Union query.
Do all query's have the same number of fields and colum order?
If so you can copy the query SQL and append a Union (or Union all) after each query like:

SELECT * FROM table1
UNION
SELECT * FROM table2
...
SELECT * FROM table10;

A Union query cannot be build in the QBE (Qyery By Example) grid you have to do it in SQL view.

 

[plog]

They come from different tables that have been input by users in differing locations

I think you have a structural issue. All this data should be in one table, that will make this issue moot and avoid further ones done the line. You've already said that 3 of the fields in these tables are the same--do all these different tables have the exact same fields?

 

[BadgerLikeSpeed]

I tried a Union query, but what I got was 3 columns with [StartDate] [EndDate] [FuelUsed], and the data in it, as opposed to a [FuelUsed] Column from each separate location.
I should maybe have been a little clearer, the start and end dates are all the same in each table as they come from week start/end dates, so what I'm trying to do is match up results from separate tables to provide an overview of weekly fuel use.

 

[BadgerLikeSpeed]

All of the data is in one table initially, with [StartDate] [EndDate] [FuelUsed] and [Location] as fields (again with others that are not relevant at the moment). I have a set of queries that takes the data and sorts it by location, so then I have 10 versions of the table. In the main table there are 10 entries for each start and end date, one for each location, as the data is pulled in from other tables.
To give you some background...
Each location is totally separate, without a consistent internet connection (meaning that I can't have some sort of web based system), which has its own database. The data from this is then exported, and then imported into my main database. This means that in the main database I have 10 results for each week, each identified by location. I have a query that then sorts this data out into 10 duplicates, one for each location. I now want to be able to get the data between a date range from each location.
Does that make any more sense?

 

[PeterF]

Does that make any more sense?

NO.

Al you want can be done in a single query, why create 10 querys.
Depending on your needs the grouping (the ∑ in the lint) can be on startdate and location and a total for the Fuel used.
You can base the filter critera on a form for easier use.
Something like:

SELECT Location, StartDate, Sum(FuelUsed) AS SumOfFuelUsed
FROM YourTableName
GROUP BY Location, StartDate
HAVING (((Location)=[Forms]![YourFormName]![NameOfLocationControl]) AND ((StartDate)=[Forms]![YourFormName]![NameOfDateControl]));