473,320 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

QUery Join Query

Hi,

I have 2 query in Ms Access.

Query1: Summary of Invoice (ZEAVI)
Fields: Biling doc
Count of biling doc
Date
Order number
Count of Order number

Query 2:Summary of PO (POPRQ)
Fields: PO
Count of PO
PO date

I link PO to Order number
but when i execute, there error message "Type mismatch in expression."

PLease help.
Jun 20 '07 #1
6 1742
kepston
97 Expert
Please post your SQL so we have an idea where it is failing.
Jun 20 '07 #2
HI,

below is the SQL statement.

SELECT [Summary of PO (POPRQ)].PO, [Summary of PO (POPRQ)].[PO date]
FROM [Summary of PO (POPRQ)] INNER JOIN [Summary of Invoice (ZEAVI)] AS [Summary of Invoice (ZEAVI)_1] ON [Summary of PO (POPRQ)].PO = [Summary of Invoice (ZEAVI)_1].[Order number]
GROUP BY [Summary of PO (POPRQ)].PO, [Summary of PO (POPRQ)].[PO date];
Jun 21 '07 #3
kepston
97 Expert
HI,

below is the SQL statement.

SELECT [Summary of PO (POPRQ)].PO, [Summary of PO (POPRQ)].[PO date]
FROM [Summary of PO (POPRQ)] INNER JOIN [Summary of Invoice (ZEAVI)] AS [Summary of Invoice (ZEAVI)_1] ON [Summary of PO (POPRQ)].PO = [Summary of Invoice (ZEAVI)_1].[Order number]
GROUP BY [Summary of PO (POPRQ)].PO, [Summary of PO (POPRQ)].[PO date];
From this, it would appear that PO and [Order number] are different datatypes.
For example, you cannot compare them directly if one is a number and the other is text.
Check the datatypes in the tables and report back.

Also, why have you created an alias of the table [Summary of Invoice (ZEAVI)]?
In this query it is unneccessary.
Jun 21 '07 #4
From this, it would appear that PO and [Order number] are different datatypes.
For example, you cannot compare them directly if one is a number and the other is text.
Check the datatypes in the tables and report back.

Also, why have you created an alias of the table [Summary of Invoice (ZEAVI)]?
In this query it is unneccessary.

Hi,

i checked the datatypes in the tables and changed according. It helps!!
Thanks alot.

but i don't quite understand what you mean by
"created an alias of the table [Summary of Invoice (ZEAVI)]?
In this query it is unneccessary"
Jun 21 '07 #5
kepston
97 Expert
...INNER JOIN [Summary of Invoice (ZEAVI)] AS [Summary of Invoice (ZEAVI)_1] ...
The AS keyword creates an alias - another name - for the table (or a field in the SELECT clause).
It is useful when doing subqueries and occasionally with complex JOINs.

I am guessing that you added the table twice in the query grid view, causing Access to give the second an alias, then deleted the first, leaving the alias.

Anyway, I'm glad it's working now.
Jun 21 '07 #6
The AS keyword creates an alias - another name - for the table (or a field in the SELECT clause).
It is useful when doing subqueries and occasionally with complex JOINs.

I am guessing that you added the table twice in the query grid view, causing Access to give the second an alias, then deleted the first, leaving the alias.

Anyway, I'm glad it's working now.

YAYA!!!

now i understand!!

THANKS alot for your help!!
=)
Jun 21 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
6
by: Steven D.Arnold | last post by:
I have a query which does not use column indexes that it should use. I have discovered some interesting behaviors of Postgres which may indicate a bug in the database's query planning. Take a...
1
by: Lumpierbritches | last post by:
I'm trying to pull all the parents of a particular animal and I have my SQL statement that says not supported and when I attempt to run the Query, I get The SQL statement couldn't be executed...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
1
by: Robert | last post by:
I am trying to create a db for service providers by county. I'm relatively new to db programming, but I have done quite a bit of programming ranging from the old basic days up to doing some...
2
by: Fendi Baba | last post by:
I created a person table with various fields such as Suffix, Salutation, etc, Some of these fields may not be mandatory for example suffix. In the actual table itself, I only have a field for...
6
by: blue875 | last post by:
A tale of two queries: One query looks like this, and runs fine. We'll call this the "Customer1 query": SELECT Customer1 overall.*, IIf(IsNull(.),0,1) AS IsField, IIf(IsNull(.),0,1) AS...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
2
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.