none
Access prediction RRS feed

  • Question

  • Hello,

    I have 2 tables with my database. The first table has  numbers for each date for the last ten years Ex. 123 was on 10/1/2013. I want to be able to see what see 2 dates and 2 days after a number/ Ex. If 123 was on 10/1/2013. I want to be able to see the number that came two days before 10/1/2013 and 2 days after 10/1/2013. So if 123 came on 10/1/2013 I want to see the number that came on 9/29/2013, 9/30/2013 and 10/2/2013 and 10/3/2013

    Tuesday, July 14, 2015 6:06 PM

Answers

  • I think this is the sort of thing you are looking for:

    PARAMETERS [Enter Number:] LONG;
    SELECT
    T1.[YourNumber] AS [Operative Number],
    T1.[YourDate] AS [Operative Date],
    T2.[YourNumber] AS [Related Number],
    T2.[YourDate] AS [Related Date]
    FROM [YourTable] AS T1, [YourTable] AS T2
    WHERE T1.[YourNumber] = [Enter Number:]
    AND T2.[YourDate] >= T1.[YourDate]-2
    AND T2.[YourDate] <= T1.[YourDate]+2
    AND T2.[YourDate] <> T1.[YourDate]
    ORDER BY T1.[YourDate], T2.[YourDate];



    Ken Sheridan, Stafford, England

    Wednesday, July 15, 2015 9:03 PM
  • Hi Ken,

    I keep getting this error:

    In operator without() in query expression 'T1. YourDate IN
    SELECT YourDate
    FROM YourTable AS T2
    WHERE T2. YourNumber=[Enter Number]

    Monday, September 7, 2015 12:29 AM
  • This possibly?

    SELECT T1.YourNumber AS [Operative Number],
    T1.YourDate AS [Operative Date],
    T2.YourNumber AS [Related Number],
    T2.YourDate AS [Related Date],
    M1.YourNumber AS [Moon Phase Number]
    FROM YourTable AS T1, YourTable AS T2, MoonPhaseTable AS M1
    WHERE T1.YourNumber = [Enter Number:]
    AND T2.YourDate >= T1.YourDate-2
    AND T2.YourDate <= T1.YourDate+2
    AND T2.YourDate <> T1.YourDate
    AND M1.YourDate = T2.YourDate
    ORDER BY T1.YourDate, T2.YourDate;


    Ken Sheridan, Stafford, England

    Monday, September 7, 2015 5:28 PM

All replies

  • Couple of questions - Do you have a number and date field (list your table structure)?    What if there is a gap in dates - do you want the nearest or next farther date?

    What is in the 2nd table?  How are they related?


    Build a little, test a little


    Tuesday, July 14, 2015 6:17 PM
  • What do you wish to use as the parameter?  If it's the date you might use a query like this:

    PARAMETERS [Enter Date:] DATETIME;
    SELECT *
    FROM [YourFirstTable]
    WHERE [YourDate] >= [Enter Date:]-2
    AND [YourDate] < [Enter Date:]+3
    ORDER BY [YourDate];

    This will prompt you for the date at runtime.  Note the way that the range is defined as on or after the start date and before the day following the end date.  This caters for the possibility of a date/time value including a non-zero time of day element, which you cannot rule out with complete confidence unless you have specifically prohibited such values in the table definition.

    If, on the other hand, the parameter is the number:

    PARAMETERS [Enter Number:] LONG;
    SELECT *
    FROM [YourFirstTable]
    WHERE [YourDate] >=
        (SELECT [YourDate]-2
         FROM [YourFirstTable]
         WHERE [YourNumber] = [Enter Number:])
    AND [YourDate] <
        (SELECT [YourDate]+3
         FROM [YourFirstTable]
         WHERE [YourNumber] = [Enter Number:])
    ORDER BY [YourDate];

    This does assume that all [YourNumber] values are distinct of course, as is implicit in your question, and are consequently indexed uniquely.

    Ken Sheridan, Stafford, England

    Tuesday, July 14, 2015 11:09 PM
  • Hello guys,

    First thank you so much for assisting me. I have made a modification, I now only have one table that consist of two fields in the table which is number and date. I have a another question as it relates to my earlier question. Currently, in my query my only criteria is my search cash 3 number which is 555. This criteria returns to me each and every time 555 occurred and date of occurrence's. What I am trying to create is a query to retrieve from my database table is the cash 3 number that occurred 1 and 2 days before and 1 and 2 days after each and every occurrence's of 5 5 5 or any other specified cash 3 number. What do I need to put in my current criteria query to get the results I want.

    Wednesday, July 15, 2015 5:53 PM
  • How will you distinguish one '555' from all the rest?

    Post data examples and what you would want the query output to look like.


    Build a little, test a little

    Wednesday, July 15, 2015 8:52 PM
  • I think this is the sort of thing you are looking for:

    PARAMETERS [Enter Number:] LONG;
    SELECT
    T1.[YourNumber] AS [Operative Number],
    T1.[YourDate] AS [Operative Date],
    T2.[YourNumber] AS [Related Number],
    T2.[YourDate] AS [Related Date]
    FROM [YourTable] AS T1, [YourTable] AS T2
    WHERE T1.[YourNumber] = [Enter Number:]
    AND T2.[YourDate] >= T1.[YourDate]-2
    AND T2.[YourDate] <= T1.[YourDate]+2
    AND T2.[YourDate] <> T1.[YourDate]
    ORDER BY T1.[YourDate], T2.[YourDate];



    Ken Sheridan, Stafford, England

    Wednesday, July 15, 2015 9:03 PM
  • Hi Ken,

    Thank you for your response. I have 17 times that 5 5 5 came in the last 20 years. I want to be able develop a criteria that looks through the database and pull out every time it came and the numbers that came two days before and 2 days after.

    For instance,

    Lets say 555 came on 10/2/2012

                 555 came on 7/2/1999

                 555 came on 2/3/1999 and soon. When I am in the design view of the query  what would I put in to see this information automatically. 

    Friday, July 17, 2015 7:05 PM
  • The last query I posted does exactly that.  It is possible to create such a query in query design view by adding two instances of the table to the design interface, but it's a lot simpler to do it in SQL view.  All you have to do is switch to SQL view and copy and paste the SQL statement I posted in place of what's there.  Then change the table and column names from YourTable, YourNumber and YourDate to your actual column names.  Do not change the aliases T1 and T2, however; these differentiate the two instances of your table.

    Ken Sheridan, Stafford, England

    Friday, July 17, 2015 10:35 PM
  • Thank you Ken,

    You are the best!!. I wanted to know what Access 2013 books or sites you recommend for me to practice so I can improve with Access. 

    Thursday, August 6, 2015 6:41 PM
  • I've always found John L. Viescas and Jeff Conrad's  Access Inside Out to be a good general primer.  As regards online resources I'm not really up to date on what's available these days in the way of basic tutorials, I'm afraid.

    Ken Sheridan, Stafford, England

    Thursday, August 6, 2015 8:47 PM
  • Thank you Ken

    Friday, August 7, 2015 5:07 PM
  • Hello Ken,

    I have a question its about excel

    I wanted to now in excel. If I put a date in Columns I12 or any other column like 8/12/2015. I want whenever I put a date in the column in the Left column A12 it automatically puts 16.667% in there and it adds on to this. Like I have  6 date columns and I want it to  equals 16.667*6. So if I finish a survey and put 8/12/2015 I get 16.667 then if I send the survey I put the date in column M12 then its 8.12.2015 It adds another 16.667 but it should be 16.667 *2 so Now in column A12 I have 32.00% How do I do this? I want it programmed whenever I add date it does this for column A12.

    Please help and thank you
      SCHEDULING   MANAGEMENT   PROGRAM ASSOCIATE   REVIEWER
    % COMPLETED   DATE ASSIGNED STAFF NAME FACILITY FACILITY TYPE SURVEY TYPE   DATE SURVEY COMPLETED DUE DATE DATE COMPLETED IN ASPEN PAST DUE   DATE RECEIVED IN THE MAIL DATE FILE SUBMITTED FOR REVIEW   DATE REVIEWED/ APPROVED DATE CLO
    Wednesday, August 12, 2015 6:21 PM
  • I'm afraid I have very little expertise in Excel, but in any event this forum is for MS Access questions.  I'd suggest that you repost the question in the Excel forum at:

    http://answers.microsoft.com/en-us/office/forum/excel?tab=Threads

    Ken Sheridan, Stafford, England

    Wednesday, August 12, 2015 8:08 PM
  • Thanks Ken, 

    I did the SQL statement you gave me also  for the other 4 tables( I just changed up the names ). I was also  working on  is it possible to tie in all for tables and have a query to show if I entered the date 12/25/2014. I want the query to show me all the numbers that populated on that date also 5 days before and 5 days after that date. For instance  I want the other tables with there information all together so I could see okay table 1 had 5 5 5 , Table 2 had 455 and Table 3 had 1 2 1 all on one query so when I enter 12/2/2014) in the parameter I see all the numbers that came within that range specified

    Thank you

    Erika 

    Thursday, August 27, 2015 8:39 PM
  • Provided that each query returns the same number of columns in the same order, and they match on data type between each query, you can tack them together in a UNION ALL operation to return a single result table.  To show which results come from which table add a column to the select clause of each which returns a different string expression as a constant in each case.

    So, if I understand your requirements correctly, the query would be along these lines:

    PARAMETERS [Enter Date:] DATETIME;
    SELECT "Table 1" AS SourceTable, YourDate, YourNumber
    FROM Table1
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
    UNION ALL
    SELECT "Table 2", YourDate, YourNumber
    FROM Table2
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
    UNION ALL
    SELECT "Table 3", YourDate, YourNumber
    FROM Table3
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
    UNION ALL
    SELECT "Table 4", YourDate, YourNumber
    FROM Table4
    WHERE YourDate BETWEEN [Enter Date:]-5 AND [Enter Date:]+5
    ORDER BY SourceTable, YourDate;

    Remember that any table or column names which inadvisedly include spaces or other special characters must be wrapped in square brackets [like this].


    Ken Sheridan, Stafford, England

    Thursday, August 27, 2015 9:47 PM
  • Thank you so much Ken
    Monday, August 31, 2015 3:23 PM
  • Ken thank you

    I was trying to incorporate another table with the (your table) the one we did earlier with the number. Where we enter a number and it shows how many times it populates.

    I wanted to tie the yourtable using the parameter we did earlier but looking for the number instead of the date. However I wanted also bring in another table to see the moonphases for when the number is entered in the parameter lets say 5 5 5. In the moonphase table I have the columns  YourDate, YourDay and YourMoonPhase Number. The name of the table is TheMoonPhase

    This is what I have and I keep getting a syntax error. Would you advise me

    PARAMETERS [Enter Number:] Text (255 );
    SELECT "YourTable" AS SourceTable, YourDate, YourNumber
    FROM YourTable
    WHERE YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+2
    ORDER BY T1.[YourDate], T2.[YourDate];
    SELECT "YourMoonPhase", YourDate, YourDay
    FROM YourMoonPhase
    WHERE [YourNumber]=[Enter Number:]
    UNION ALL
    ORDER BY SourceTable, YourNumber;

    Thank you,

    Ken

    Monday, August 31, 2015 8:52 PM
  • 1.  You have the UNION ALL line in the wrong place; it should be immediately before the second SELECT clause.

    2.  You are declaring one parameter, [Enter Number:], but then also referencing another, [Enter Date:].  You should declare both.  The former should be declared as whatever data type the YourNumber column is, e.g. LONG if it's a long integer, and the second as DATETIME.

    3.  You cannot include YourNumber in the ORDER BY clause if, as I'd expect, the YourNumber and YourDay columns are of different data types, and even if they are of the same data type the resulting order in which the rows are returned is unlikely to be meaningful.  Bear in mind that in the result table both the YourNumber and YourDay values will be returned under the YourNumber column heading, so you might want to alias the YourNumber column in the first query as something more generic.

    Ken Sheridan, Stafford, England

    Monday, August 31, 2015 9:52 PM
  • ken,

    The Yourtable we did about a week or so ago is perfect. I just want to pull in this moonphase part. I changed my Column name in the MoonPhaseTable to (YourDate, MoonPhaseTable, YourNumber). I want to be able to enter a number and date and it pulls the the number like the one about a week ago and then grab the date from both tables along with the moonphase. 

    This is my sql script I edited and did the changes you said. Thank you. However, I believe I missing something because I keep getting that error message. 

    PARAMETERS [Enter Number:] Text(255);
    SELECT "YourTable" AS SourceTable, YourDate, YourNumber
    FROM YourTable
    WHERE YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+2
    UNION ALL
    SELECT "YourMoonPhase"AS SourceTable, YourDate, MoonPhaseTable
    FROM YourMoonPhase
    WHERE [YourNumber]=[Enter Number:]
    ORDER BY SourceTable, YourDate;


    Tuesday, September 1, 2015 6:39 PM
  • SELECT "YourMoonPhase"AS SourceTable, YourDate, MoonPhaseTable

    FROM YourMoonPhase


    The above is missing a space before AS and seems to have transposed the table and column names.  I think it should be:

    SELECT "MoonPhaseTable" AS SourceTable, YourDate, YourMoonPhase
    FROM MoonPhaseTable

    You seem to have taken no note of what I said in my previous post with regard to the parameter declarations, however.  I find it hard to believe that the YourNumber column contains text values of up to 255 characters length.

    To be honest, I haven't a clue what you are trying to achieve.  It may well be a JOIN rather than a  UNION ALL operation which is required here.  Perhaps it would help if you were to post the real definitions of the two tables, and an example of the result set you want to return.


    Ken Sheridan, Stafford, England

    Tuesday, September 1, 2015 7:18 PM
  • Hi TheSpecialistErika,

    Thanks for your post. I am not sure whether your current issue is the same with your original issue? If they are the same issue, please feel free to share with us what you want. If not, I will suggest you post a new thread for this, and then there would be more community members to help you.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 2, 2015 5:39 AM
  • Hi Ken, 

    Thank you I did a join query. I wanted to know. How can I still get the parameter box to pop up and enter a number instead of entering it in the  design view and If I want to see 1 day past the date the number came on and  2 days before.  I attached my screen shot of the join query I did below. Also is there away in access that I can automatically update my number columns (my numbers are from another website and I want to know is it possible if access can automatically pulled the numbers into my database once I open the database everyday. 

    Wednesday, September 2, 2015 1:50 PM
  • I never envisaged you'd actually have a table named YourTable with columns YourDate etc.  I only used those names in my original reply as generic terms, on the assumption that you'd replace them with your real table and column names, the former being a meaningful name which describes the entity type being modelled by the table, the latter describing the attribute of that entity type which each column represents.

    As regards the parameter you either enter the expression as the criterion for the relevant column in query design view, or you enter the WHERE clause in SQL view, which you previously seem to have done with:

        WHERE YourDate BETWEEN [Enter Date:]-1 AND [Enter Date:]+2

    As regards updating the values in a table with on-line data, it may be possible, it may not, but even if it is, it will require strong programming skills on your part.  With respect, your posts to date do not suggest that you have these.  On the contrary you do appear to be struggling with some of the most basic concepts and mechanisms of relational database development.  I think you need to invest some time learning both the principles of the database relational model, and the nuts and bolts of MS Access itself.  Otherwise I'm afraid you will continue to flounder.  While we can assist you with specific issues, we cannot give you the essential skill base required for developing relational database applications.  That will only come through study on your part, and it would doing you no service to suggest that the learning curve is not a steep one.

    Ken Sheridan, Stafford, England

    Wednesday, September 2, 2015 2:26 PM
  • Will do Ken. I am still new to Access. Thank you

    Below is my sql so far. I want to be able for this sql search 2 days before and 1 day after. For instance, if I enter 5 5 5 and that number came on 10/31/2006. I want to see the numbers that came on 11/1/2006 and 11/2/ 2006 and 10/30/2006. As you see I m putting my moonphase table in. I want to know can I do a DataAdd fuction and if so how would I write the statement



    SELECT YourTable.YourNumber, YourTable.YourTable, YourTable.YourDate, MoonPhaseTable.MoonPhaseTable, MoonPhaseTable.YourNumber
    FROM YourTable INNER JOIN MoonPhaseTable ON YourTable.YourDate = MoonPhaseTable.YourDate
    WHERE (((YourTable.YourNumber)=[Enter Number]));

    Please help me and thank you

    Wednesday, September 2, 2015 8:54 PM
  • Maybe this?

    SELECT T1.YourNumber, T1.YourDate,
    MoonPhaseTable.YourNumber AS MoonPhaseNumber
    FROM YourTable AS T1 INNER JOIN MoonPhaseTable
    ON T1.YourDate = MoonPhaseTable.YourDate
    WHERE T1.YourDate IN
        (SELECT YourDate
         FROM YourTable AS T2
         WHERE T2.YourNumber = [Enter Number]
         UNION ALL
         SELECT YourDate-1
         FROM YourTable AS T3
         WHERE T3.YourNumber = [Enter Number]
         UNION ALL
         SELECT YourDate+1
         FROM YourTable AS T4
         WHERE T4.YourNumber = [Enter Number])
    ORDER BY T1.YourDate;

    Note that I've left the references to YourTable.YourTable and MoonPhaseTable.MoonPhaseTable out of the query's SELECT clause.  Surely you don't have columns with the same name as the tables?

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, September 2, 2015 10:06 PM
    Wednesday, September 2, 2015 10:04 PM
  • Hi Ken,

    I keep getting this error:

    In operator without() in query expression 'T1. YourDate IN
    SELECT YourDate
    FROM YourTable AS T2
    WHERE T2. YourNumber=[Enter Number]

    Monday, September 7, 2015 12:29 AM
  • There should be no space after the dot operator in T1.YourDate, T1.YourNumber etc.

    Ken Sheridan, Stafford, England

    Monday, September 7, 2015 11:13 AM
  • Hi Ken,

    I tried but still no luck. So Below is my sql from my other query. What could I write to join my MoonPhaseTable to the sql below. My fields in the Date, Number and  Day

    PARAMETERS [Enter Number:] Text ( 255 );
    SELECT T1.YourNumber AS [Operative Number], T1.YourDate AS [Operative Date], T2.YourNumber AS [Related Number], T2.YourDate AS [Related Date]
    FROM YourTable AS T1, YourTable AS T2
    WHERE (((T1.YourNumber)=[Enter Number:]) AND ((T2.YourDate)>=[T1].[YourDate]-2 And (T2.YourDate)<=[T1].[YourDate]+2 And (T2.YourDate)<>[T1].[YourDate]))
    ORDER BY T1.YourDate, T2.YourDate;

    Monday, September 7, 2015 3:21 PM
  • This possibly?

    SELECT T1.YourNumber AS [Operative Number],
    T1.YourDate AS [Operative Date],
    T2.YourNumber AS [Related Number],
    T2.YourDate AS [Related Date],
    M1.YourNumber AS [Moon Phase Number]
    FROM YourTable AS T1, YourTable AS T2, MoonPhaseTable AS M1
    WHERE T1.YourNumber = [Enter Number:]
    AND T2.YourDate >= T1.YourDate-2
    AND T2.YourDate <= T1.YourDate+2
    AND T2.YourDate <> T1.YourDate
    AND M1.YourDate = T2.YourDate
    ORDER BY T1.YourDate, T2.YourDate;


    Ken Sheridan, Stafford, England

    Monday, September 7, 2015 5:28 PM
  • Thank you Ken, 

    I know the one above will pull 3 numbers because its pulling from the table with three numbers. With my other table that has 4 numbers I have the sql below. Is it a way to see the numbers that came in the table with four numbers when I enter  number for the table with 3 numbers. 

    For instance, I enter 5 5 5 and the date comes 10/31/2006 and the number 212 from the day before and 432 for the two days later and a moonphase .98. Now could it be shown what numbers came when I enter 555 for the table with 4 numbers.Example, I enter 5 5 5, dates 10/31/2006. So it saw 555 then in my table with 4 numbers said okay when 5 5 5 enter it brings 2 3 3 4 and it should still have the same date  and moon phase  as the table with 3 numbers. Is this possible?

    PARAMETERS [Enter Number:] Text ( 255 );
    SELECT T1.YourNumber AS [Operative Number], T1.YourDate AS [Operative Date], T2.YourNumber AS [Related Number], T2.YourDate AS [Related Date]
    FROM Cash4Table AS T1, Cash4Table AS T2
    WHERE (((T1.YourNumber)=[Enter Number:]) And ((T2.YourDate)>=T1.YourDate-2 And (T2.YourDate)<=T1.YourDate+2 And (T2.YourDate)<>T1.YourDate))
    ORDER BY T1.YourDate, T2.YourDate;

    Wednesday, September 9, 2015 7:26 PM
  • Example, I enter 5 5 5, dates 10/31/2006. So it saw 555 then in my table with 4 numbers said okay when 5 5 5 enter it brings 2 3 3 4 and it should still have the same date  and moon phase  as the table with 3 numbers.

    Sorry, but I haven't a clue what that means.

    Ken Sheridan, Stafford, England

    Wednesday, September 9, 2015 9:36 PM
  • Sorry, but I haven't a clue what that means.


    Ken Sheridan, Stafford, England

    Please excuse the interruption...

    I wonder if the OP means the two Table (3 numbers) and (4 numbers) can be related 1 to 1 so that the value 555 will always show the data {10/31/2006} in (3 number) and {2 3 3 4} in (4 number)

    Please excuse me if I am incorrect.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, September 9, 2015 9:55 PM
  • Thanks, Chris; you could be right, but to be honest, I've been struggling to understand the OP's requirements throughout this thread because of the way they are expressed.

    Ken Sheridan, Stafford, England

    Wednesday, September 9, 2015 10:33 PM
  • Hi guys,

    The sql below it works great. I wanted to know if its possible for me to pull another table in  this sql  based on the information below. For instance, if I typed a number into my parameter from the query below could it recognized the dates and pulled the numbers in from another query based on the dates and numbers from the query below. I hope this is clear. Thank you both


    SELECT T1.YourNumber AS [Operative Number], 
    T1.YourDate AS [Operative Date], 
    T2.YourNumber AS [Related Number], 
    T2.YourDate AS [Related Date],
    M1.YourNumber AS [Moon Phase Number]
    FROM YourTable AS T1, YourTable AS T2, MoonPhaseTable AS M1
    WHERE T1.YourNumber = [Enter Number:] 
    AND T2.YourDate >= T1.YourDate-2 
    AND T2.YourDate <= T1.YourDate+2 
    AND T2.YourDate <> T1.YourDate
    AND M1.YourDate = T2.YourDate
    ORDER BY T1.YourDate, T2.YourDate;
    Thursday, September 10, 2015 4:31 PM
  • I wanted to know if its possible for me to pull another table in  this sql  based on the information below.
    Provided there are columns on which the two can be joined, the answer is probably yes.  You might find it simpler to create a new query joining the two queries, rather than trying to expand the above.

    Ken Sheridan, Stafford, England

    Thursday, September 10, 2015 5:12 PM
  • Ken,

    I made some adjustments

    Tuesday, June 21, 2016 8:22 PM
  • Ken,

    This is my design view above. Where I have[ enter Number] I put 801 in I want to see the numbers and the dates that came two days before and 1 day after the given number I put in. How can I include that in my parameter for that information to populate 

    Thank you


    Tuesday, June 21, 2016 8:24 PM
  • I'm sorry, but I can make neither head nor tail of that.  Despite my drawing your attention earlier to the fact, you continue to use meaningless column names like YourNumber.  As I've pointed out before I only used terms like that in my original responses As 'placeholders' because I didn't know the actual object names.  I expected you to replace these with the actual names which reflect the nature of the attribute which the column represents.  You also have column names which include the substring 'table', which makes no sense.

    I don't think I can be of any further assistance in the context of this thread, so am discontinuing monitoring of the thread

    Ken Sheridan, Stafford, England

    Wednesday, June 22, 2016 10:02 AM