SQL Problem

[Garyj]

Can anyone help me with this. I keep getting the following message. "The number of columns in the two selected tables or queries of a union query don't match".

I looked at the table and the two queries and they both have a total of 18 fields. When I count the number in my code I come up with 18.

SELECT 0 AS TRANSACTIONID, DateAdd("d",-1,[Forms]![Enter].[StartDate]) AS TRANSACTIONDATE, [tbl2311014].METERID, "OPENING BALANCE" AS SOURCENAME, [tbl2311014].LOGONID, "EnteredBy" AS LOGONID, [tbl2311014].VERIFY, "VERIFIEDBY" AS VERIFY, Sum([tbl2311014].DEPOSIT) AS SumOfDeposit, Sum([tbl2311014].CHECK001) AS SumOfCheck001, Sum([tbl2311014].CHECK002) AS SumOfCheck002, Sum([tbl2311014].CHECK003) AS SumOfCheck003, Sum([tbl2311014].CHECK005) AS SumOfCheck005, Sum([tbl2311014].CHECK006) AS SumOfCheck006, Sum([tbl2311014].REINVESTNOTICES) AS SumOfReinvestNotices, Sum([tbl2311014].SOA) AS SumOfSOA, Sum([tbl2311014].COSARS) AS SumOfCOSARS, Sum([tbl2311014].COCOAS) AS SumOfCOCOAS, Sum([tbl2311014].TDMAIL) AS SumOfTDMAIL, Sum([tbl2311014].MISC) AS SumOfMISC

FROM tbl2311014 INNER JOIN qryOpbal2311014 ON [tbl2311014].TRANSACTIONID = qryOpbal2311014.TRANSACTIONDATE
GROUP BY 0, DateAdd("d",-1,[Forms]![Enter].[StartDate]), [tbl2311014].METERID, "OPENING BALANCE", [tbl2311014].VERIFY;

UNION SELECT [tbl2311014].TRANSACTIONID, [tbl2311014].TRANSACTIONDATE, [tbl2311014].METERID, [tbl2311014].SOURCENAME, [tbl2311014].LOGONID, [tbl2311014].VERIFY, [tbl2311014].DEPOSIT, [tbl2311014].CHECK001, [tbl2311014].CHECK002, [tbl2311014].CHECK003, [tbl2311014].CHECK005, [tbl2311014].CHECK006, [tbl2311014].REINVESTNOTICES, [tbl2311014].SOA, [tbl2311014].COSARS, [tbl2311014].COCOAS, [tbl2311014].TDMAIL, [tbl2311014].MISC , *
FROM tbl2311014

WHERE ((([tbl2311014].TransactionDate) Between [forms].[Enter].[StartDate] And [forms]![Enter].[EndDate]));

I hope someone can help me with this.

Thank you in advance

 

[Kodo]

in a union query, all queries must have the same number of fields selected.

your bottom query has * in it. That selects all of the fields and depending on how many you have, it may or may not work. You're going to have to revise your query.

 

[Garyj]

in a union query, all queries must have the same number of fields selected.

your bottom query has * in it. That selects all of the fields and depending on how many you have, it may or may not work. You're going to have to revise your query.

Could you help with an example. I looked at both queries and the one table that is referrence and all of them have 18 fields.

Thanks

 

[Kodo]

just take out the *.you're effectivey adding another 18 fields with it.

 

[Garyj]

just take out the *.you're effectivey adding another 18 fields with it.

I am not using *. in my queries. I'm grabbing all fields separately. Would you like for me too post a sample of what I am doing? If I can. I always have trouble posting samples because the file size is to big. In any case just like me know and I'll try.

Thanks

 

[IanH]

I am not using *. in my queries. I'm grabbing all fields separately. Would you like for me too post a sample of what I am doing? If I can. I always have trouble posting samples because the file size is to big. In any case just like me know and I'll try.

Thanks

Got to agree with Kodo. Your original code (as posted) was in the UNION part as follows ...

UNION SELECT [tbl2311014].TRANSACTIONID, [tbl2311014].TRANSACTIONDATE, [tbl2311014].METERID, [tbl2311014].SOURCENAME, [tbl2311014].LOGONID, [tbl2311014].VERIFY, [tbl2311014].DEPOSIT, [tbl2311014].CHECK001, [tbl2311014].CHECK002, [tbl2311014].CHECK003, [tbl2311014].CHECK005, [tbl2311014].CHECK006, [tbl2311014].REINVESTNOTICES, [tbl2311014].SOA, [tbl2311014].COSARS, [tbl2311014].COCOAS, [tbl2311014].TDMAIL, [tbl2311014].MISC , *
FROM tbl2311014


The final , * means that you are adding the individual fields, referenced separately, and then adding all fields from tbl2311014 again.

Ian

 

[Garyj]

Got to agree with Kodo. Your original code (as posted) was in the UNION part as follows ...

UNION SELECT [tbl2311014].TRANSACTIONID, [tbl2311014].TRANSACTIONDATE, [tbl2311014].METERID, [tbl2311014].SOURCENAME, [tbl2311014].LOGONID, [tbl2311014].VERIFY, [tbl2311014].DEPOSIT, [tbl2311014].CHECK001, [tbl2311014].CHECK002, [tbl2311014].CHECK003, [tbl2311014].CHECK005, [tbl2311014].CHECK006, [tbl2311014].REINVESTNOTICES, [tbl2311014].SOA, [tbl2311014].COSARS, [tbl2311014].COCOAS, [tbl2311014].TDMAIL, [tbl2311014].MISC , *
FROM tbl2311014


The final , * means that you are adding the individual fields, referenced separately, and then adding all fields from tbl2311014 again.

Ian

OK, I have the same code running in a different db and it runs great. If I remove the , * I still get the same error "tables or queries does not match". What do you sugguest?

Thanks

 

[Garyj]

OK, I have the same code running in a different db and it runs great. If I remove the , * I still get the same error "tables or queries does not match". What do you sugguest?

Thanks

I have attached a sample of my db. I would appreciate any help on this matter.

 

[IanH]

OK, I have the same code running in a different db and it runs great. If I remove the , * I still get the same error "tables or queries does not match". What do you sugguest?

Thanks

I've had another look at your code.

You are specifying the LOGINID and VERIFY fields twice in the first part of the select statement.

The first LOGINID comes from [tbl2311014].LOGONID, the second is "EnteredBy".

The first VERIFY field is [tbl2311014].VERIFY, the second "VERIFIEDBY" .

This means that you actually have 20 fields in the first part.

If you remove the , * and one of the redundant fields you should, hopefully, get the right number of columns.

Ian

 

[Garyj]

I've had another look at your code.

You are specifying the LOGINID and VERIFY fields twice in the first part of the select statement.

The first LOGINID comes from [tbl2311014].LOGONID, the second is "EnteredBy".

The first VERIFY field is [tbl2311014].VERIFY, the second "VERIFIEDBY" .

This means that you actually have 20 fields in the first part.




If you remove the , * and one of the redundant fields you should, hopefully, get the right number of columns.

Ian

Thank you for your time everything is working.