UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Creating Query, Access 2013    
 
   
msnarayanan
post Feb 18 2018, 06:10 AM
Post#1



Posts: 277
Joined: 28-May 13



dear all

i would like to get your help as to how to build a query based on two tables.

i have the following fields:
table 1 : product_name, soh, basic cost 1 hp motor 20 15000
table 2 product_name sold invoice 1 hp motor 10 10000

the above is an example. by creating a query, i want to know the soh and sold related to 1 hp motor as follows:

product-name soh sold
1 hp motor 20 10

i tried query wizaed but it shows only one field.

your hellp please

m s narayanan




Go to the top of the page
 
Jeff B.
post Feb 18 2018, 08:47 AM
Post#2


UtterAccess VIP
Posts: 10,128
Joined: 30-April 10
From: Pacific NorthWet


Those field names suggest that this data is organized like it would be in a spreadsheet. While Access can work with (around) that, both you and Access will work harder than necessary with that, compared to if the data were well-normalized.

You mention that your query is not returning what you want. Please show us the SQL-statement of your query -- open the query in design view and change the view to SQL.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
msnarayanan
post Feb 19 2018, 03:38 AM
Post#3



Posts: 277
Joined: 28-May 13



dear mr jeff

as required by you i am attaching the SQL for a query.

to clarify the fields, i give below the details:

acc_name - from account master table
acc_name - from Receipts table
dr_amount - from Receipts table
cr_amount - from Payments table

i thought that the system will pick up acc_name from the account master table, it being the primary key.

you may please correct it and help me out.

M S NarayananAttached File  sql_query.zip ( 8.91K )Number of downloads: 8

Go to the top of the page
 
Jeff B.
post Feb 19 2018, 09:26 AM
Post#4


UtterAccess VIP
Posts: 10,128
Joined: 30-April 10
From: Pacific NorthWet


Perhaps another of the folks here at UA will open your attachment and offer ideas. I don't open objects from the internet...

(if you copy the SQL and paste it into a reply, I'll be happy to take a look at it)

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
msnarayanan
post Feb 19 2018, 11:15 AM
Post#5



Posts: 277
Joined: 28-May 13



SELECT [account master table].acc_head AS [account master table_acc_head], [Trading Debit Query].acc_head AS [Trading Debit Query_acc_head], [Trading Debit Query].dr_amount, [Trading Credit Query].cr_amount
FROM ([account master table] INNER JOIN [Trading Credit Query] ON [account master table].[acc_no] = [Trading Credit Query].[acc_head]) INNER JOIN [Trading Debit Query] ON [account master table].[acc_no] = [Trading Debit Query].[acc_head];

dear mr jeff

as per your suggestion, i am copying the SQL.

seeking your help

m s narayanan
Go to the top of the page
 
Jeff B.
post Feb 20 2018, 08:26 AM
Post#6


UtterAccess VIP
Posts: 10,128
Joined: 30-April 10
From: Pacific NorthWet


A quick glance showed a pair of joins between fields named [acc_no] and [acc_head]. Are these the correct fields to join between the tables?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
msnarayanan
post Feb 20 2018, 09:54 AM
Post#7



Posts: 277
Joined: 28-May 13



dear Mr Jeff
yes.
acc_no is prime key
related to acc_head in two other table.

I want to create a query to show dr_amount and cr_amount which are in different tables.

M S Narayanan
Go to the top of the page
 
Jeff B.
post Feb 21 2018, 08:51 AM
Post#8


UtterAccess VIP
Posts: 10,128
Joined: 30-April 10
From: Pacific NorthWet


In your initial post, the field both tables appear to have in common is [product_name]. Why does the SQL statement try to connect/join via [acc_no] & [acc_head]?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
msnarayanan
post Feb 21 2018, 09:34 AM
Post#9



Posts: 277
Joined: 28-May 13



dear Mr Jeff
my initial post was just an example.
but the SQL represents for a real dB on which I am working.


m s narayanan
Go to the top of the page
 
Jeff B.
post Feb 22 2018, 07:53 AM
Post#10


UtterAccess VIP
Posts: 10,128
Joined: 30-April 10
From: Pacific NorthWet


It would probably help folks here help you if you describe the actual tables/fields, rather than "an example". That way, we can see the underlying data structure and compare that with the SQL statement.

The SQL you posted is doing something, right? What is it doing? What isn't it doing that you want to have happen?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2018 - 12:17 AM