Table join query

[moseleya]

Hi all,

A newbie at access - so im help there is an expert out there that can help.

I have joined two tables - the first is my prime data, the second is hwere i am trying to get one part of the data from. The issue i have is the second table has several version of the data, to which i am then getting severeal lines back in my query and it then giving me a false calculation, where i only want one line back. Does anyone know a way around this?

Thanks
Antiny

[alansidman]

Are you able to be more specific? Perhaps some sample data of what you have, what you are getting and what you ideally want. Also, the current SQL statement so we can see exactly what you are doing. Without data, we are throwing darts blindfolded after being spun around a couple of times.

[moseleya]

hi,
sorry- i will try and explain
Table 1 has codes with a Qty, table 2 has the same codes but with other data, however sometimes the code is sometimes in more than once. In the query i am getting back answers every time there is a matching value rather than just the once. Example

Table 1 Table 2
Code Qty Code Sales
Code a 500 Code a 15.5
Code b 100 Code a 15.5
Code c 150 Code b 19

Query 1
Code Qty Sales
Code a 500 15.5
Code a 500 15.5
Code b 100 19

So when I then try to sum Qtys’ I am getting incorrect data

I hope this make sense

[alansidman]

You need to make two aggregate queries and then join the two aggregate queries.

Please Login or Register  to view this content.

[moseleya]

HI,

I have just now learnt about SQL part of databse.
Below is what i have

Please Login or Register  to view this content.

[alansidman]

How does your SQL statement relate to the information you posted in thread 3. I don't see any correlation. Did you try the three queries I provided and if so, did it work for you?

Alan

[moseleya]

HI,

This is actually the SQL from the query i have. I tried to post a summaried version of what i had and i think i should have just posted the above orgionally

Antony

[alansidman]

I'll ask again. How does this query that you provided relate to what you showed in thread 3. The field names are not the same so I do not know what you are showing me in the query. Again, you asked for a solution in thread 3 and I gave you one. Did it work? If not, what issues did you encounter.

[moseleya]

Hi Alan,

Sorry if i am not explain this well.
I wasnt expecting an answer back that involved SQL coding - being a newbie to access i dont know what i was expecting, but i have never used SQL data, therefore I have gone to my actual query and copied the SQL coding that is there, so any names of tables or fields are actual and true

Antony

[alansidman]

I understand that what you have provided is the actual query. But I need a road map to equate what is in you actual query to the fields that you named in thread three. I do not have a crystal ball. This is my final ask on this: did the queries I supplied work? did you try them? You are not giving me any information I can use to help you.

[moseleya]

I have still have the same issues - but what i have posted is the actual coding from the query i have.
Just imagine i have not posted thread three, but the problem is with the coding i have actually posted.

Based on the orgional issue - Table 1 is now EXCESS and Table 2 is now cta2DATA. Code is now product_code (in first table) and code is STOCKCODE (in second table)
I have the same issue, but can not work out how to apply what you have given me to my actual coding taken from my query

[alansidman]

Substitute the names for your tables and fields from your query into the ones I gave you based upon your original description. Do this is a word document or in notepad. Open Access. Open a new query. When it offers to supply and insert a table click on close. A SQL window will open. Paste your first query in and then save it. Repeat this for the second and third query. Once you have done this, run your third query. It should give you the results you requested based upon your original posting.