This is my first time here. After doing long hours of researching, I can not come across a solution to my (relatively simple) problem.
I have 2 tables:
The table "Product" has the following data:
cod_product / cod_franchise
2 / 1
2 / 1
3 / 3
The table "Item" has the following rows:
id_item / cod_product
1 / 1
2 / 2
3 / 2
I need to identify how many times each franchise appears (through its respective cod_product) in the "Item" table.
Therefore Im running the following SQL code:
SELECT P.COD_FRANCHISE, COUNT (I.COD_PRODUCT) AS 'QUANTITY'
FROM PRODUCT P
INNER JOIN ITEM I ON P.COD_PRODUCT = I.COD_PRODUCT
GROUP BY P.COD_FRANCHISE
But all I get with MS Access is a "TYPE MISMATCH IN EXPRESSION" ERROR Message.
Can someone please help me?
Best,
I've tried your query (copy/paste the code) and it works perfectly as it is.
My guess is that one of your tables' field is set to a different data type than the others . Open each table on creation mode and change all your data types to numeric or text, whichever ,as long as they're all the same.
If you don't know how/don't want to change the data types you can use this query:
SELECT P.COD_FRANCHISE*1, COUNT(I.COD_PRODUCT) AS ['QUANTITY'] FROM PRODUCT AS P INNER JOIN ITEM AS I ON P.COD_PRODUCT*1=I.COD_PRODUCT*1 GROUP BY P.COD_FRANCHISE;
The "*1" put everywhere will force Access to change your data type.