Results 1 to 9 of 9
  1. #1
    dbohn is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    5

    Query to round up value from one table to nearest pre-defined "stock size" from another table


    Here is my situation…I have an append query where one of the fields that it brings in is [OpeningHeight] from a table called [tbl_Hardwood Finish_Drawers]. What I am trying to do is create an expression in this append query to calculate the “Box Size” by rounding up the [OpeningHeight] to the next equal or greater value of predefined “stock” sizes in a table called [tbl_Hardwood Finish_Drawers_Stock Heights]…Field Name in that table is [StockHeight]. Both of these fields ([OpeningHeight] and [StockHeight] are currently “Short Text” data types. This was the easiest way for me to display these sizes as fractions rather than decimals – but they can be changed to a number format if that is necessary for this to work. Any suggestions on how I can accomplish this would be greatly appreciated.Thanks!

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Take a look at this site. http://allenbrowne.com/round.html

    HTH

  3. #3
    dbohn is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    5
    Thanks burrina - I was actually on that webpage a couple times already. A lot of good information - but I still don't see how to round to pre-defined numbers from another table. For example, my "stock" sizes that I have listed in "tbl_Hardwood Finish_Drawers_Stock Heights" that I want to round to are...2, 3, 6, 8, 9, 12, 16. So if the value of the field that I want to round up is "10", I want it to enter "12" in my "Box Size" field.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think will have to do a DMin() expression:

    DMin("StockHeight", "[tbl_Hardwood Finish_Drawers_Stock Heights]", "StockHeight>" & [OpeningHeight])

    Yes, the fields will have to be number type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    dbohn is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    5
    Thanks June7 - that looked promising when I read it over the weekend. This morning I changed the appropriate fields to Number format (and added the fractions that were dropped in this conversion back in with decimals). When I try to run the query with this expression added - I get... "Syntax error (missing operator) in query expression 'Stockheight>'." Here is what the expression looks like in my query... BoxH: DMin("StockHeight","[tbl_Hardwood Finish_Drawers_Stock Heights]","StockHeight>" & [OpeningHeight])I have checked spelling in this expression - and all matches my table names and field names.Any thoughts??

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I see nothing wrong with the DMin syntax.

    Post the entire query SQL statement.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    dbohn is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    5
    Thanks for the follow-up June7. Here is the SQL statement that you requested.

    INSERT INTO [apdtbl_Hardwood Finish_Drawers_Weekly] ( RUN, [PROD#], Floorplan, [Wood Color], [Ardis Names], Location, FrontWidth, FrontLength, Qty, NotesFilter, OpeningHeight, OpeningWidth, OpeningDepth, NotesProduction, PRODID, BoxH )
    SELECT DISTINCT [tbl_PURCHASING WEEKLY SCHEDULE].RUN, [tbl_PURCHASING WEEKLY SCHEDULE].[PROD#], [tbl_PURCHASING WEEKLY SCHEDULE].[COMBINED FP], [tbl_PURCHASING WEEKLY SCHEDULE].[Wood Description ], [tbl_ProdWOOD COLORS].[Ardis Names], [tbl_Hardwood Finish_Drawers].Location, [tbl_Hardwood Finish_Drawers].FrontWidth, [tbl_Hardwood Finish_Drawers].FrontLength, [tbl_Hardwood Finish_Drawers].Qty, [tbl_Hardwood Finish_Drawers].NotesFilter, [tbl_Hardwood Finish_Drawers].OpeningHeight, [tbl_Hardwood Finish_Drawers].OpeningWidth, [tbl_Hardwood Finish_Drawers].OpeningDepth, [tbl_Hardwood Finish_Drawers].NotesProduction, [tbl_PURCHASING WEEKLY SCHEDULE].ID, DMin("StockHeight","[tbl_Hardwood Finish_Drawers_Stock Heights]","StockHeight>" & [OpeningHeight]) AS BoxH
    FROM ([tbl_PURCHASING WEEKLY SCHEDULE] LEFT JOIN [tbl_ProdWOOD COLORS] ON [tbl_PURCHASING WEEKLY SCHEDULE].[Wood Description ] = [tbl_ProdWOOD COLORS].[Production Order Wood Desc]) LEFT JOIN [tbl_Hardwood Finish_Drawers] ON [tbl_PURCHASING WEEKLY SCHEDULE].[COMBINED FP] = [tbl_Hardwood Finish_Drawers].Floorplan
    GROUP BY [tbl_PURCHASING WEEKLY SCHEDULE].RUN, [tbl_PURCHASING WEEKLY SCHEDULE].[PROD#], [tbl_PURCHASING WEEKLY SCHEDULE].[COMBINED FP], [tbl_PURCHASING WEEKLY SCHEDULE].[Wood Description ], [tbl_ProdWOOD COLORS].[Ardis Names], [tbl_Hardwood Finish_Drawers].Location, [tbl_Hardwood Finish_Drawers].FrontWidth, [tbl_Hardwood Finish_Drawers].FrontLength, [tbl_Hardwood Finish_Drawers].Qty, [tbl_Hardwood Finish_Drawers].NotesFilter, [tbl_Hardwood Finish_Drawers].OpeningHeight, [tbl_Hardwood Finish_Drawers].OpeningWidth, [tbl_Hardwood Finish_Drawers].OpeningDepth, [tbl_Hardwood Finish_Drawers].NotesProduction, [tbl_PURCHASING WEEKLY SCHEDULE].ID
    HAVING ((([tbl_PURCHASING WEEKLY SCHEDULE].RUN)=[ENTER RUN#]));

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I still can't see anything wrong with the DMin expression.

    I don't often use query objects for action SQL, I use VBA to run the SQL.

    This is the most complex INSERT SELECT I've ever seen. Why have DISTINCT and GROUP BY in the same SQL? There are no aggregate calcs, GROUP BY seems unnecessary.

    Sorry, afraid this is beyond my ability to analyse remotely.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    dbohn is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2014
    Posts
    5
    OK - thanks for your efforts on this June7. Yes - I am a bit of a novice with Access. Some of my applications have outgrown what I have been doing in Excel over the past 10 years - so I have successfully moved many of them (over the past year) into Access with great results. Perhaps some formal training is in order before I dig too much deeper into this one though. Thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 02-07-2014, 11:57 AM
  2. Replies: 4
    Last Post: 10-16-2013, 01:39 AM
  3. Replies: 1
    Last Post: 03-14-2013, 12:39 PM
  4. Replies: 1
    Last Post: 12-14-2012, 12:32 AM
  5. Replies: 3
    Last Post: 03-14-2012, 12:33 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums