User Profile

Collapse

Profile Sidebar

Collapse
NDayave
NDayave
Last Activity: Oct 27 '17, 03:35 PM
Joined: Feb 12 '07
Location:
  •  
  • Time
  • Show
  • Source
Clear All
new posts
  • NDayave
    replied to Looking for some help with JOIN logic
    in SQL Server
    Never mind, sorted it with help from here.

    Final syntax was (Take note of the WHERE clause addition at the bottom):

    Code:
    FROM a 
    INNER JOIN (b AS b1
                LEFT OUTER JOIN b AS b2
                    ON(b1.resource_id = b2.resource_id
                        AND b1.sequence_no < a2.sequence_no))
        ON(a.resource_id = b.resource_id) 
    INNER JOIN c
    ...
    See more | Go to post

      Leave a comment:

    • NDayave
      started a topic Looking for some help with JOIN logic
      in SQL Server

      Looking for some help with JOIN logic

      Good Day,

      I have a number of tables that need to be joined to gether and up to now everything has been fine. I now need to exclude old files that are identified by a sequence number; newer records have a higher number.

      The joins currently look like this:

      Code:
      FROM a
	INNER JOIN b
		ON(a.resource_id = b.resource_id)
	INNER JOIN c
		ON(a.resource_id = c.apar_id)
	INNER
      ...
      See more | Go to post
      • NDayave
        started a topic How to get a cumulative total of fields up to a date specified in each record?
        in SQL Server

        How to get a cumulative total of fields up to a date specified in each record?

        Hello,

        I would like to be able to calculate a sum of a particular field for all records that have a date before the current record.

        I would like this to work for each record to essentially provide a cumulative total along the records returned.

        For Example:
        Code:
        |#|Date----|Total
|-|--------|-----
|1|1/1/2011|0
|3|3/1/2011|1
|2|3/1/2011|1
|5|7/1/2011|6
        ...
        See more | Go to post
        • NDayave
          replied to Deleted Image displayed when in Runtime but not Full Access
          in Access
          There is apparently a command line switch to force operation in the runtime - Adding /runtime to the shortcut IIRC - but I have never been able to get it to work.

          As for installing both on one PC, it is possible. You just need to specify which version you open files in. (Right Click -> Open With)

          It can also be uninstalled separately as well.


          If you can't use the runtime, here is the difference. Top...
          See more | Go to post

            Leave a comment:

          • NDayave
            replied to Deleted Image displayed when in Runtime but not Full Access
            in Access
            It is called "FormDialog " and utilises the modules "API_GetTextMet rics" and "Dialog".

            In the version you have it should open whenever the dialog.box command is used, so if you try to close the Load/Save Mailing List form, Export the selected Mailing List etc.

            If it doesn't open or seem to be functioning in an apparently normal fashion, I'll upload a different version.
            See more | Go to post

              Leave a comment:

            • NDayave
              replied to Deleted Image displayed when in Runtime but not Full Access
              in Access
              I feel that I should make it clear that I am not changing any of the Images on the form. I am not deleting or inserting at any time. This concerns static, embedded images that should not be changing.


              The custom Message Box is a form that is formatted on it's Load to meet the requirements specified in the code.


              The 4 images are called "imgExclamation ", "imgQuestio n" etc. and are embedded...
              See more | Go to post

                Leave a comment:

              • NDayave
                replied to Deleted Image displayed when in Runtime but not Full Access
                in Access
                No offence, but that link is totally unrelated.
                I am using embedded bitmaps, not linked Jpegs.

                My problem is that one image is displayed when I open the form in a full version of access, whereas when opened in the Access Runtime, an older image that has been deleted is displayed.


                This is persistent. I can open the same file in runtime and full version and see completely different images over and over again....
                See more | Go to post

                  Leave a comment:

                • NDayave
                  started a topic Deleted Image displayed when in Runtime but not Full Access
                  in Access

                  Deleted Image displayed when in Runtime but not Full Access

                  How Do,

                  I am using custom message boxes to make them much more aesthetically pleasing. On this form, there are 4 images for "Question", "Informatio n", "Critical" and "Exclamatio n".

                  I found the custom message boxes at http://blog.nkadesign.com/2008/ms-ac...x-replacement/ and edited it to fit my needs - Background colours, borders, etc as well as the Images displayed....
                  See more | Go to post
                  • NDayave
                    replied to Inserting blank records to query result for label printing
                    in Access
                    I have found the simple little trick I was after.

                    On the 'Rows to skip' form, store the row numbers in a global string variable with a comma at the start and end of each number, Eg: ",2,4,6,7,9 ,"

                    In the Report, enter the following code and the specified rows will be passed over, continuing with the next row in the recordsource:

                    Code:
                    Option Compare Database
Option Explicit

Dim fBlankNext
                    ...
                    See more | Go to post

                      Leave a comment:

                    • NDayave
                      replied to Inserting blank records to query result for label printing
                      in Access
                      This database will only be used by one user at a time, so I don't have to worry about multiple users.

                      In order to maintain simplicity, I will have the form resemble the Label paper, allowing the user to click the labels that they wish to pass over. If recordset processing is to be used, why bother with the Tables? Seems like an extra step in the process.

                      My original thoughts were to have the form look like the label paper,...
                      See more | Go to post

                        Leave a comment:

                      • NDayave
                        replied to Inserting blank records to query result for label printing
                        in Access
                        Yes, Mary is correct; I want to be able to specify and 'pass over' any labels that are not suitable for printing on the sheet.

                        I am not so worried about how to determine which labels to pass over, more how to add in either the blank rows to the query result or blank detail sections in the report.
                        See more | Go to post

                          Leave a comment:

                        • NDayave
                          replied to Inserting blank records to query result for label printing
                          in Access
                          We'd thought about temporary tables, but as this is a regular operation that can involve hundreds of rows we are trying to avoid it.

                          I also want to be able to skip certain labels in case they have ruined some on a sheet (See post original post).

                          I am hoping for a solution that can add in blank rows into the SQL, which I know is possible in other platforms.
                          See more | Go to post

                            Leave a comment:

                          • NDayave
                            replied to Inserting blank records to query result for label printing
                            in Access
                            That would be very kind of you Pat.

                            Even if it doesn't work I'm sure I'll learn something new.
                            See more | Go to post

                              Leave a comment:

                            • NDayave
                              replied to Inserting blank records to query result for label printing
                              in Access
                              I have attached the relevant parts of the List generator and stripped most other things out. The lists can still be generated as they would normally.

                              I have PM'd the Zip password.

                              Let me know what you think....
                              See more | Go to post

                                Leave a comment:

                              • NDayave
                                replied to Inserting blank records to query result for label printing
                                in Access
                                Well, I've made a very idiot proof query generator for use by the most computer illiterate people around. It functions by making sentences that correspond to the generated SQL.

                                Eg: Include | Organisations | Working in the Area of | Schools

                                returns the SQL that brings out all schools.

                                From here they can add more groups of individuals/organisations by adding more "Include" criteria, remove certain inds/orgs...
                                See more | Go to post

                                  Leave a comment:

                                • NDayave
                                  replied to Inserting blank records to query result for label printing
                                  in Access
                                  Basically, I have a mailing list generator that produces a list of addresses based on the criteria selected.

                                  Ie: Forename, Surname, Address1, Address2, etc, etc

                                  The query is generated via VBA code based on the selections made by the user. As this involves both Individual Contacts and Organisations, there is the possibility of a Union being used, but not always.

                                  Why would it require a full page worth of labels?...
                                  See more | Go to post

                                    Leave a comment:

                                  • NDayave
                                    started a topic Inserting blank records to query result for label printing
                                    in Access

                                    Inserting blank records to query result for label printing

                                    How do,

                                    I have a form that outputs addresses in a format that can be printed on to 3x7 label paper for envelopes.

                                    What I want is a way to enter blank (or " ") rows to the query result where the user specifies so no label is printed in that particular place.

                                    I am aware of the problems of inserting blank records to tables. I say this as the other forums I have searched all reply with disbelief...
                                    See more | Go to post
                                    • NDayave
                                      started a topic Simulate Full Screen or Dim the Background
                                      in Access

                                      Simulate Full Screen or Dim the Background

                                      In looking into transparent forms, I came across a rather simple solution to the "Run in Fullscreen" feature severely lacking in Access, adapted from the MSDN article at Modal Dialogs with Transparent Backgrounds (MSDN)

                                      If you simply want a solid colour to fill the whole screen, make a form with that colour Detail and set:
                                      - Scroolbars = Neither
                                      - Record Selectors = No
                                      - Navigation Buttons = No
                                      - Dividing...
                                      See more | Go to post
                                      • NDayave
                                        replied to SELECT CASE WHEN IN() statement
                                        in SQL Server
                                        Cheers for that, made it "LEFT(POSTCODE, 2) IN ('KW', 'W1', 'W2', etc)" which also clears up some ambiguity in the selection process Eg: 'L%' and 'LA%'

                                        Thanks again,

                                        NDayave
                                        See more | Go to post

                                          Leave a comment:

                                        • NDayave
                                          started a topic SELECT CASE WHEN IN() statement
                                          in SQL Server

                                          SELECT CASE WHEN IN() statement

                                          Hi,

                                          I have a query that is sorting people into their respective areas of the country and need to assign each postcode the correct label (Eg: 'North', 'Wales', etc.). I am trying to do this in a CASE statement at the moment but it does not like the IN() part, returning all the values with the 'N/A' label. Everything parses fine and the query runs, it just doesn't assign the correct labels.

                                          Code:
                                          SELECT	PH.SERIALNUMBER,
	PH.PLEDGEID,
                                          ...
                                          See more | Go to post
                                          No activity results to display
                                          Show More
                                          Working...