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
Does this one work? -
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];
you should wrap any code you post in [code] blocks
and put some effort into making it readable
see Posting Guidlines 24 2385
Post the SQL of the query for one of your repeated attempts
- 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];
Does this one work? -
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];
you should wrap any code you post in [code] blocks
and put some effort into making it readable
see Posting Guidlines
can you put it as SQL to me... i tried it still the same error
NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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.
@Delerna
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 32,556
Expert Mod 16PB
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).
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
This is getting beyond a joke...
And the answer to the second question is??
Sir
I am really not joking..........came to net when my friend could not solve problem in real scenario.
Just looking for ans... that it
NeoPa 32,556
Expert Mod 16PB
So why haven't you answerd the second question from post #6 then? I've asked it and referred back to it over & over again and you post back but still haven't answered it. It may not be a joke, but it's strange nevertheless, and I can't help you properly without that information.
Do you want everything matching the dates in the Date table?... Date table I had created since I thought it will help me to create query properly.
Actually I have data as given post 10 and 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 32,556
Expert Mod 16PB @goalv
That's an unfortunate answer. It's clear and very helpful, but I'd have preferred a different one. Having such a table would mean we could use it as the master table. All the other tables could link to that as subordinates and you'd have your (relatively) easy solution.
I will need to go back over post #10 & reformat it now to show the data more clearly now I know it's important.
The trouble with your answer is that it relies on a FULL OUTER JOIN to work correctly. Access doesn't support FULL OUTER JOINs :( There is a way around it though. We will need to get into UNION queries, which are supported in Access only as simple SQL. They cannot be displayed graphically as most simpler queries can.
I will outline the steps now, as I have to kick-off at 19:30 (45 mins) across town.
UNION the three tables together, ensuring all required data is available. Use separate columns for the data from each table that is quantities. Each table will include two columns that are simply set to 0.
Group the records together by [Date] and [Material] then you have the basis for producing your data.
Let us know how you get on with this and if you need more help. If so please explain what your problem is & what you tried. Have fun.
NeoPa
I have made following SQL : - SELECT RMSTORE.MAT,
-
RMSTORE.QTY,
-
RMSTORE.DATE
-
FROM RMSTORE;
-
-
UNION
-
SELECT RECEIPT.MAT,
-
RECEIPT.QTY,
-
RECEIPT.DATE
-
FROM RECEIPT;
-
-
UNION
-
SELECT ISSUE.MAT,
-
ISSUE.QTY,
-
ISSUE.DATE
-
FROM ISSUE
-
GROUP BY MAT,
-
QTY,
-
DATE;
Now out is given as MAT, QTY, DATE as per my selection ...and my sample data is coming correctly... but that has not solved my problem since for analysis I want data output in below format -
DATE MATERIAL DS-TABLE REC-TABLE ISSUE-TABLE
-
On 01/01/10 24XX14 6 2 1
-
01/01/10 24XX24 0 9 1
today I have learned some thing new thanks to you.. kindly help further
NeoPa 32,556
Expert Mod 16PB
Let's start by making a few points clear : - I have updated your posts on a few occasions now to add code tags around your code. Please don't make me have to do any more. You must follow the rules and use them when posting code.
- It is also a good idea to use them for posting data, but you will not be punished for not doing this.
- Look at your code now. I have laid it out so that it is readable and makes sense. You benefit from this as the problems are much more clear and obvious in this, more readable, format.
- Technical items :
The semi-colon (;) should be at the end of the SQL, not at the end of each SQL phrase (as you have it). I'm surprised you even got this to work as it is. - You may not notice it, but your use of UNION is not quite correct. You should be using UNION ALL.
- When you only have a single input (FROM clause) within a SQL phrase it is not necessary (and is not helpful) to include the table name as a qualifier to each field. A better approach is simply to show the field itself. Unqualified and surrounded in brackets [].
- You have not gone the whole way. You have left out the last part.
All that said, you have made an impressive start. Don't let my criticisms put you off. They are there for you information and learning. You have made a good start.
I will go away now to put together some SQL which will help you further. Check back later.
NeoPa 32,556
Expert Mod 16PB
The inner query would be something like : -
SELECT [Date],
-
[Mat] AS [Material],
-
[QTY] AS [DSQty],
-
0 AS [RecQty],
-
0 AS [IssueQty]
-
FROM RMSTORE
-
-
UNION ALL
-
SELECT [Date],
-
[Mat] AS [Material],
-
0 AS [DSQty],
-
[QTY] AS [RecQty],
-
0 AS [IssueQty]
-
FROM RECEIPT
-
-
UNION ALL
-
SELECT [Date],
-
[Mat] AS [Material],
-
0 AS [DSQty],
-
0 AS [RecQty],
-
[QTY] AS [IssueQty]
-
FROM ISSUE
-
-
ORDER BY [Date],
-
[Material];
From there, you would wrap this up in a GROUP BY query (Use a sub-query - Subqueries in SQL).
I'll look at setting something up for you. Check back later.
NeoPa 32,556
Expert Mod 16PB
The overall query would be something like : -
SELECT [Date],
-
[Material],
-
Sum([DSQty]) AS [DS-Table],
-
Sum([RecQty]) AS [Rec-Table],
-
Sum([IssueQty]) AS [Issue-Table]
-
-
FROM (
-
SELECT [Date],
-
[Mat] AS [Material],
-
[QTY] AS [DSQty],
-
0 AS [RecQty],
-
0 AS [IssueQty]
-
FROM RMSTORE
-
-
UNION ALL
-
SELECT [Date],
-
[Mat] AS [Material],
-
0 AS [DSQty],
-
[QTY] AS [RecQty],
-
0 AS [IssueQty]
-
FROM RECEIPT
-
-
UNION ALL
-
SELECT [Date],
-
[Mat] AS [Material],
-
0 AS [DSQty],
-
0 AS [RecQty],
-
[QTY] AS [IssueQty]
-
FROM ISSUE
-
-
ORDER BY [Date],
-
[Material]
-
) AS subQ
-
-
GROUP BY [Date],
-
[Material];
Neopa
Thank a lot .............it worked perfectly to my requirement ,I will use the posting as text book topic.
If you plan to visit my country check : www.goa-tourism.com NeoPa 32,556
Expert Mod 16PB
I'm happy to help LV.
I'm sure Goa is a lovely place to visit (I've heard from people who have). I'll have to add it to my list of places to go one day.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a...
|
by: Miguelito Bain |
last post by:
hi everybody.
i'm trying to find out what you can and can't do with/in calculated
fields. any help, pointers, and/or advice would be greatly
appreciated. i'm a newbie, but i want to learn, and...
|
by: Takeadoe |
last post by:
Hey Gang, I'm gearing up to retool for the upcoming deer season here in
Ohio and I could use some help with very general questions about
direction. I will be scanning nearly 210,000 forms that...
|
by: shapper |
last post by:
Hello,
I have the tables content and content_localized in a Microsoft Access
database.
I created a Query in my Microsoft Access database named
"content_SELECT":
SELECT...
|
by: hardik |
last post by:
hi friends i need help in this sql query
i have table like,
id fid
__ _____
autonumber text
and i am storing values like
|
by: fiona |
last post by:
Reading, Berkshire, UK 05 June 2007 - Crainiate Software make details
available of the release of Objecto Framework 2.0, an upgrade to their
enterprise business component framework, designed to...
|
by: Sid |
last post by:
I hope someone could help me with this.
I am trying to setup a criteria to decide when to allow/not allow
user
to click on the check box. logically it looks simple but I am not
able
to...
|
by: Orgil |
last post by:
Hi all,
I have just writing a program named "DayBook".
I am using MS Access 2003 database and Microsoft .NET C# 2005 (with
framework 2.0). MS Access 2003, MS .NET C# 2005 and dotNetFramework2.0...
|
by: .nLL |
last post by:
Erorr is
---------------------
Microsoft VBScript runtime error '800a0046'
Permission denied
/a.asp, line 3
-----------------------
|
by: jsacrey |
last post by:
Hey everybody, got a secnario for ya that I need a bit of help with.
Access 97 using linked tables from an SQL Server 2000 machine.
I've created a simple query using two tables joined by one...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
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....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |