473,320 Members | 2,088 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Microsoft Access Query help

11
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
Expand|Select|Wrap|Line Numbers
  1. DATE             STORE    
  2. ID  DATE         ID  MATERIAL CODE  QTY  DATE
  3. 1  1/1/10         1  24XX14          6  1/1/10
  4. 2  1/2/10         2  24XX14          3  1/2/10
  5. 3  1/3/10         3  24XX14          3  1/3/10
  6. 4  1/4/10         4  25AA25          4  1/14/10
Expand|Select|Wrap|Line Numbers
  1.      3                              4    
  2.   RECEIPT                         ISSUE    
  3. ID  RM CODE  QTY  DATE      ID  RM CODE  QTY   DATE
  4. 1  24XX14   200  1/1/10      1  24XX14   200  1/1/10
  5. 2  24XX24  9000  1/1/10      2  24XX24  9000  1/1/10
  6. 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
Expand|Select|Wrap|Line Numbers
  1. DATE   MATERIAL CODE  STORE qty  RECEIPT QTY  ISSUE QTY
  2. 1/1/10  24XX14            6           6           1
  3. 1/1/10  24XX24            0        9000           0
  4. 1/2/10  25AA25            4           4           4
Jan 20 '10 #1

✓ answered by Delerna

Does this one work?

Expand|Select|Wrap|Line Numbers
  1. Select date.date, 
  2.     rmstore.[sap code], 
  3.     receipt.date, 
  4.     receipt.qty, 
  5.     rmstore.qty, 
  6.     receipt.[sap code]
  7. from [date] 
  8. left join rmstore on date.date = rmstore.date
  9. left join receipt on rmstore.[sap code] = receipt.[sap code]
  10. 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
Delerna
1,134 Expert 1GB
Post the SQL of the query for one of your repeated attempts
Jan 21 '10 #2
goalv
11
Expand|Select|Wrap|Line Numbers
  1. Select date.date, rmstore.[sap code], receipt.date, receipt.qty, rmstore.qty, receipt.[sap code]
  2. from ([date] left join rmstore on date.date = rmstore.date) left join receipt on rmstore.[sap code] = receipt.[sap code]
  3. order by date.date, rmstore.[sap code];
Jan 21 '10 #3
Delerna
1,134 Expert 1GB
Does this one work?

Expand|Select|Wrap|Line Numbers
  1. Select date.date, 
  2.     rmstore.[sap code], 
  3.     receipt.date, 
  4.     receipt.qty, 
  5.     rmstore.qty, 
  6.     receipt.[sap code]
  7. from [date] 
  8. left join rmstore on date.date = rmstore.date
  9. left join receipt on rmstore.[sap code] = receipt.[sap code]
  10. 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
Jan 21 '10 #4
goalv
11
can you put it as SQL to me... i tried it still the same error
Jan 25 '10 #5
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.
Jan 25 '10 #6
goalv
11
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
Jan 28 '10 #7
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.
Jan 28 '10 #8
Delerna
1,134 Expert 1GB
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.
Jan 28 '10 #9
goalv
11
@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
Expand|Select|Wrap|Line Numbers
  1. ID   CODE  QTY  DATE
  2.  1  24XX14  6  01/01/2010
  3.  2  24XX14  3  02/01/2010
  4.  3  24XX14  3  03/01/2010
  5.  9  24XX24  2  28/01/2010
  6. 10  25AA25  4  14/01/2010
  7. 11  25AA25  4  16/01/2010
  8. 17  25AA28  8  06/01/2010
  9. 18  25AA28  8  07/01/2010
RECEIVED TABLE
Expand|Select|Wrap|Line Numbers
  1. ID  SAP CODE  QTY    DATE
  2.  1  24XX14    2    01/01/2010
  3.  2  24XX24    9    01/01/2010
  4.  3  25AA25    2    01/01/2010
  5.  4  25AA28    3    02/01/2010
  6.  5  25XX18    2    03/01/2010
  7.  6  27XX22    2    04/01/2010
  8.  7  27XX24    3    02/01/2010
  9.  8  28XX20    3    02/01/2010
  10.  9  28XX25    2.5  02/01/2010
ISSUE TABLE
Expand|Select|Wrap|Line Numbers
  1. ID  SAP CODE  QTY   DATE
  2.  1  24XX24      1  01/01/2010
  3.  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.
Expand|Select|Wrap|Line Numbers
  1.       DATE     MATERIAL   DS-TABLE  REC-TABLE  ISSUE-TABLE
  2. On  01/01/10    24XX14       6          2           1
  3.     01/01/10    24XX24       0          9           1
  4.     01/01/10    25AA25       0          2
Jan 29 '10 #10
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).
Jan 29 '10 #11
goalv
11
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
Jan 30 '10 #12
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.
Jan 30 '10 #13
goalv
11
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
Jan 31 '10 #14
NeoPa
32,556 Expert Mod 16PB
This is getting beyond a joke...

And the answer to the second question is??
Feb 1 '10 #15
goalv
11
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
Feb 1 '10 #16
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.
Feb 2 '10 #17
goalv
11
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.
Expand|Select|Wrap|Line Numbers
  1. DATE         MATERIAL DS-TABLE REC-TABLE ISSUE-TABLE
  2. On 01/01/10  24XX14      6         2          1
  3. 01/01/10     24XX24      0         9          1
  4. 01/01/10     25AA25      0         2
Feb 2 '10 #18
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.
Feb 2 '10 #19
goalv
11
NeoPa

I have made following SQL :
Expand|Select|Wrap|Line Numbers
  1. SELECT   RMSTORE.MAT,
  2.          RMSTORE.QTY,
  3.          RMSTORE.DATE
  4. FROM     RMSTORE;
  5.  
  6. UNION
  7. SELECT   RECEIPT.MAT,
  8.          RECEIPT.QTY,
  9.          RECEIPT.DATE
  10. FROM     RECEIPT;
  11.  
  12. UNION
  13. SELECT   ISSUE.MAT,
  14.          ISSUE.QTY,
  15.          ISSUE.DATE
  16. FROM     ISSUE
  17. GROUP BY MAT,
  18.          QTY,
  19.          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
Expand|Select|Wrap|Line Numbers
  1. DATE         MATERIAL DS-TABLE REC-TABLE ISSUE-TABLE
  2. On 01/01/10  24XX14      6         2          1
  3. 01/01/10     24XX24      0         9          1
today I have learned some thing new thanks to you.. kindly help further
Feb 3 '10 #20
NeoPa
32,556 Expert Mod 16PB
Let's start by making a few points clear :
  1. 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.
  2. It is also a good idea to use them for posting data, but you will not be punished for not doing this.
  3. 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.
  4. 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.
  5. You may not notice it, but your use of UNION is not quite correct. You should be using UNION ALL.
  6. 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 [].
  7. 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.
Feb 4 '10 #21
NeoPa
32,556 Expert Mod 16PB
The inner query would be something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Date],
  2.          [Mat] AS [Material],
  3.          [QTY] AS [DSQty],
  4.          0 AS [RecQty],
  5.          0 AS [IssueQty]
  6. FROM     RMSTORE
  7.  
  8. UNION ALL
  9. SELECT   [Date],
  10.          [Mat] AS [Material],
  11.          0 AS [DSQty],
  12.          [QTY] AS [RecQty],
  13.          0 AS [IssueQty]
  14. FROM     RECEIPT
  15.  
  16. UNION ALL
  17. SELECT   [Date],
  18.          [Mat] AS [Material],
  19.          0 AS [DSQty],
  20.          0 AS [RecQty],
  21.          [QTY] AS [IssueQty]
  22. FROM     ISSUE
  23.  
  24. ORDER BY [Date],
  25.          [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.
Feb 4 '10 #22
NeoPa
32,556 Expert Mod 16PB
The overall query would be something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Date],
  2.          [Material],
  3.          Sum([DSQty]) AS [DS-Table],
  4.          Sum([RecQty]) AS [Rec-Table],
  5.          Sum([IssueQty]) AS [Issue-Table]
  6.  
  7. FROM     (
  8.     SELECT   [Date],
  9.              [Mat] AS [Material],
  10.              [QTY] AS [DSQty],
  11.              0 AS [RecQty],
  12.              0 AS [IssueQty]
  13.     FROM     RMSTORE
  14.  
  15.     UNION ALL
  16.     SELECT   [Date],
  17.              [Mat] AS [Material],
  18.              0 AS [DSQty],
  19.              [QTY] AS [RecQty],
  20.              0 AS [IssueQty]
  21.     FROM     RECEIPT
  22.  
  23.     UNION ALL
  24.     SELECT   [Date],
  25.              [Mat] AS [Material],
  26.              0 AS [DSQty],
  27.              0 AS [RecQty],
  28.              [QTY] AS [IssueQty]
  29.     FROM     ISSUE
  30.  
  31.     ORDER BY [Date],
  32.              [Material]
  33.     ) AS subQ
  34.  
  35. GROUP BY [Date],
  36.          [Material];
Feb 4 '10 #23
goalv
11
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
Feb 7 '10 #24
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.
Feb 8 '10 #25

Sign in to post your reply or Sign up for a free account.

Similar topics

1
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...
1
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...
4
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...
0
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...
1
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
0
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...
8
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...
0
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...
0
by: .nLL | last post by:
Erorr is --------------------- Microsoft VBScript runtime error '800a0046' Permission denied /a.asp, line 3 -----------------------
6
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...
0
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...
0
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...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
1
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)...
1
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....
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.