Microsoft Access Query help

[goalv]

Hi friends I am bit new to access , I want to make query for data which has not happened after repeated attempts...can any one help me

I have following tables

DATE             STORE    
ID  DATE         ID  MATERIAL CODE  QTY  DATE
1  1/1/10         1  24XX14          6  1/1/10
2  1/2/10         2  24XX14          3  1/2/10
3  1/3/10         3  24XX14          3  1/3/10
4  1/4/10         4  25AA25          4  1/14/10

     3                              4    
  RECEIPT                         ISSUE    
ID  RM CODE  QTY  DATE      ID  RM CODE  QTY   DATE
1  24XX14   200  1/1/10      1  24XX14   200  1/1/10
2  24XX24  9000  1/1/10      2  24XX24  9000  1/1/10
3  25AA25  2500  1/1/10      3  25AA25  2500  1/1/10

want to make query as given below which will list all the code in store table as per date / selected date

DATE   MATERIAL CODE  STORE qty  RECEIPT QTY  ISSUE QTY
1/1/10  24XX14            6           6           1
1/1/10  24XX24            0        9000           0
1/2/10  25AA25            4           4           4

[Delerna]

Post the SQL of the query for one of your repeated attempts

[goalv]

Select date.date, rmstore.[sap code], receipt.date, receipt.qty, rmstore.qty, receipt.[sap code]
from ([date] left join rmstore on date.date = rmstore.date) left join receipt on rmstore.[sap code] = receipt.[sap code]
order by date.date, rmstore.[sap code];

[Delerna]

Does this one work?

[code=sql]
Select date.date,
rmstore.[sap code],
receipt.date,
receipt.qty,
rmstore.qty,
receipt.[sap code]
from [date]
left join rmstore on date.date = rmstore.date
left join receipt on rmstore.[sap code] = receipt.[sap code]
order by date.date, rmstore.[sap code];[/code]
you should wrap any code you post in [code] blocks
and put some effort into making it readable

see Posting Guidlines

[goalv]

can you put it as SQL to me... i tried it still the same error

[NeoPa]

I find your example unhelpful as it seems to be missing various lines. When I try to work out what you are trying to say the information is not there.

Do you want all records shown in the output for all dates, regardless of whether they are found in Store, Receipt or Issue?

Do you want everything matching the dates in the Date table?

You need to be very clear of exactly what you require before answering. Database work does not work very well with approximations.

[goalv]

Data is in tables
Store : ID MATERIAL CODE QTY DATE
RECEIPT : ID RM CODE QTY DATE
ISSUE : ID RM CODE QTY DATE

I want all records to be shown in the output for all dates, regardless of whether that material is found in Store, Receipt or Issue
e.g
say
Date Material Store ( quantity ). Receipt ( quantity ) Issue ( quantity )
01/01/2010 24XX24 5 2 2

[NeoPa]

Please read my questions again. Neither has been answered (though I appreciate you did try). It's more about the Dates table. I know you want any record that occurs in any of the other tables. What is not clear is what you want when there is a Date record but no materials, or Materials of any sort without a matching Date record.

BTW - calling a table [Date] is a recipe for confusion. You should possibly consider giving it a more sensible name. [tblDate] is a good suggestion.

[Delerna]

can you put it as SQL to me... i tried it still the same error

also, and this might be important.
What, exactly, is the error?
"still the same error" doesn't tell me much. Well it doesn't tell me anything really.
Judging from your first post, the query I posted, should very nearly give you what you want.



What does "can you put it as SQL to me" mean?
I did post it as SQL


I have just gone over the data in your first post (after neopa code blocked it)
One thing I noticed is that the values in your "desired result sample" don't match the values in your "data samples"
for example
Receipt and issue table for 25AA25 on 1/1/10 says 2500
But in your desired result it says 4 for both.
Is that just a slip up when you typed the numbers or am I missing something.

goalv
We are not tring to pick on your post and we are eager to assist you, but
You must bear in mind that we don't have any background detail surrounding your project.
All we know about it is what you tell us.
Therefore you do need to be clear, concise, complete and accurate in what you post. Otherwise we will end up trying to fix the wrong problem for you and you will leave saying those guys don't know what they are talking about.

[goalv]

also, and this might be important.
What, exactly, is the error?
"still the same error" doesn't tell me much. Well it doesn't tell me anything really.
Judging from your first post, the query I posted, should very nearly give you what you want.



What does "can you put it as SQL to me" mean?
I did post it as SQL


I have just gone over the data in your first post (after neopa code blocked it)
One thing I noticed is that the values in your "desired result sample" don't match the values in your "data samples"
for example
Receipt and issue table for 25AA25 on 1/1/10 says 2500
But in your desired result it says 4 for both.
Is that just a slip up when you typed the numbers or am I missing something.

goalv
We are not tring to pick on your post and we are eager to assist you, but
You must bear in mind that we don't have any background detail surrounding your project.
All we know about it is what you tell us.
Therefore you do need to be clear, concise, complete and accurate in what you post. Otherwise we will end up trying to fix the wrong problem for you and you will leave saying those guys don't know what they are talking about.

Delerna and Neopa

thank for being patient with me

Again posting my Table , hoping that I have conveyed the message properly

Delivery Schedule table

ID   CODE  QTY  DATE
 1  24XX14  6  01/01/2010
 2  24XX14  3  02/01/2010
 3  24XX14  3  03/01/2010
 9  24XX24  2  28/01/2010
10  25AA25  4  14/01/2010
11  25AA25  4  16/01/2010
17  25AA28  8  06/01/2010
18  25AA28  8  07/01/2010

RECEIVED TABLE

ID  SAP CODE  QTY    DATE
 1  24XX14    2    01/01/2010
 2  24XX24    9    01/01/2010
 3  25AA25    2    01/01/2010
 4  25AA28    3    02/01/2010
 5  25XX18    2    03/01/2010
 6  27XX22    2    04/01/2010
 7  27XX24    3    02/01/2010
 8  28XX20    3    02/01/2010
 9  28XX25    2.5  02/01/2010

ISSUE TABLE

ID  SAP CODE  QTY   DATE
 1  24XX24      1  01/01/2010
 2  24XX14      1  01/01/2010

I want single output : which will give me on given date a)how much particular material was scheduled b) How much that material is received C) how much that material is issued

For eg.

      DATE     MATERIAL   DS-TABLE  REC-TABLE  ISSUE-TABLE
On  01/01/10    24XX14       6          2           1
    01/01/10    24XX24       0          9           1
    01/01/10    25AA25       0          2

[NeoPa]

I really don't see how this post helps. It doesn't explain whether this new information is different from the old. It doesn't respond to the points already made (Have you understood them). If the data is the same then it says nothing. If it's different then which is to be relied upon? Either way it is now up to us to go through both large sets of data to determine whether they are different or not, as you don't explain what you've done, or what knew understandings you have. You don't explain what, if anything, that either of us has said that you are responding to.

All in all we are expected to work quite hard just to get half the information you should have explained in your post. Not a very workable way to proceed in my view.

The [Date] table has also simply disappeared, but without any explanation as to why - or any answers to the questions posed (already more than once).

[goalv]

Neopa

I have those three table. For my daily analysis I want to know on a date, how much as particular material code was scheduled for receipt , how much it has been received in stores and how much it has been issued from stores

Trying my best to convey what I want

[NeoPa]

I know you're trying. It would be helpful though, if you could simply answer the questions I asked in post #6.

The reason for this is that there are actually multiple answers to your question, depending on what your requirement actually is, and your explanations, to date, have not clarified that. Repeatedly starting from scratch in the hope that your newest explanation will clarify the situation is not very productive, as you don't understand the fundamental issue. Hence, I asked some simple questions, the answers of which I know should clarify the point.

[goalv]

Do you want all records shown in the output for all dates, regardless of whether they are found in Store, Receipt or Issue?

YES....I want output for all dates, regardless of whether they are found in Store, Receipt or Issue

[NeoPa]

This is getting beyond a joke...

And the answer to the second question is??