Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2021
    Posts
    5

    Lightbulb Sum of reserved material - day-by-day

    Hey guys,


    looks like there has been too much time since my studying days, for you this should be pretty easy:
    I do have a pretty large rental database of the following format:

    equipA equipB fromdate toDate
    order1 1 2
    order2 6 3
    order3 4 2
    (basically table of rental contracts with period and number of equipments used)

    What I need is this day-by-day aggregation of the equipement in use:

    1.1. 2.1. 3.1.
    equipA 2 3 3
    equipB 1 5 3
    (how many equipments of type A,B,... are in use on each single day)

    Thanks for any help in Access or SQL.

    Cheers Thorsten

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post your tables and relationships to help put your request into context.
    Some sample data would also be helpful.

  3. #3
    Join Date
    Apr 2021
    Posts
    5
    Quote Originally Posted by orange View Post
    Please post your tables and relationships to help put your request into context.
    Some sample data would also be helpful.
    Thank you orange, but no need to post my entire database, all relevant data and relationship is in my post: One simple table containing orders with the numbers of different types of equipment needed, and the period of time. As a result, I would need a query giving me the sum of each type of material (only 3or 4) in use/reserverd on a given date. Similar or even better would be a result table with all equipment types as rows, all dates as colums, and the sum of equipment of that type on that date as cell value. But this would be an (appreciated) extra, values for a specific date would already be of great help.
    I read that it might be necessary to set up a temp table (in MySQL, SQL), if you know how, I would give it a try outside Access. Thanks for any help

  4. #4
    Join Date
    Apr 2021
    Posts
    5
    Source:
    order equip1 equip2 equip3 fromDate toDate
    1 2 4 2 2nd mai 2020 15th june 2020
    2 3 6 3 1st january 2020 14th march 2020
    3 1 0 9 10th october 2020 15th october 2020

    Result needed:
    equipmentType 1st May 2nd May 3rd May
    equip1 12 1 3
    equip2 5 5 17
    equip3 16 0 22
    (Sums are totally random)
    Hope you guys send some good ideas, thanks.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You are asking for help in obtaining results from specific tables.

    In order to do this it helps if you upload the actual tables so that we as the helpers do not have to recreate the tables.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    a) You redesign your database and have tables like tblOrders: OrderID, OredrNo, FromDate, ToDate, ... and tblOrderRows: OrderRowID, OrderID, EquipmentType, UsedQty. Additionally you'll also need a table like tblRentalTerms: RentalTerm, or tblRentalTerms, OrderID, RentalTerm in case different orders may have different date sets as rental terms. And then you can create a report, which lists equipment quantities for every equipment type grouped by rental terms.

    b) You keep your current data structure, ditch Access, and use Excel instead (this doesn't mean this structure doesn't cause problems for you in Excel too - such tables are from 18th century, i.e. a paper-form).

  7. #7
    Join Date
    Apr 2021
    Posts
    5
    I'll give it a new try: I received that massive data dump on behalf of my client in one huge table and should do some reporting on it. I'M WELL AWARE, that this would be a no go in database design, and that even the data dump could have been done in a more intelligent way. But I'm not asking for best practice examples, just a solution for one-time reporting. No relations, joins, ect to be considered, just one massive database.
    The only relevant columns and fields are as described:

    order Num. equip1 Num. equip2 Num. equip3 fromDate toDate
    1 2 4 2 2nd mai 2020 15th june 2020
    2 3 6 3 1st january 2020 14th march 2020
    3 1 0 9 10th october 2020 15th october 2020

    I can easily query numbers of used material on a specific date, but I'm not able to aggregate that into one table like this:

    equipmentType Sum. 1st May Sum. 2nd May Sum. 3rd May...
    equip1 12 1 3
    equip2 5 5 17
    equip3 16 0 22

    For you DB specialists, this should be pretty easy to build in ACCESS or SQL.

    Thanks for any targeted help

    Thorsten

  8. #8
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    It's not easy because the data isn't stored in a sensible (normalised) fashion.
    Access is not good at handling spreadsheet style data, because of that.

    You can get what you want using a Power Query in Excel which can transform the data into a more sensible layout and then easily produce the pivoted results you are after.
    If it's a one off exercise that is definitely the way to go.

    If you need to repeat the exercise regularly then it would require a very specific set of VBA processes to extract and save the data in a normalised fashion, then query it to give you the desired output.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Thorsten,
    This is doable, but will take planning and VBA programming. (at least for me).
    But you need to provide enough information.

    From Post #3:
    Quote Originally Posted by TheMaramatanga View Post
    Thank you orange, but no need to post my entire database, all relevant data and relationship is in my post....
    From Post #1:
    - (basically table of rental contracts with period and number of equipment used)
    - What I need is this day-by-day aggregation of the equipment in use




    From Post Post #7:
    Quote Originally Posted by TheMaramatanga View Post
    ...., just one massive database.
    In the "just one massive database", is it an Excel file or an Access table? How many columns/fields are there? With the "Order", "fromDate", "toDate" fields... that is 3 fields. From your example, you only have 3 pieces of equipment?

    I think it will be necessary to use a temp table to get the usage by date. But an Access table is limited to 255 fields. You say you want the usage by date. (1 May, 2 May, 3May, ,...). That will require 365 days + 1 field for the date + 1 field for equip type/number... a total or 367 fields/columns.
    So you will need to use SQL Server table or Excel (Excel can have 16,384 columns / SQL Server can have 1024 fields).

    It would be very helpful to have 10 - 20 records/rows of the data file to be able to test the VBA results.



    Not sure how you will be able to report the results - don't know any way to have a report with ~367 columns.
    Maybe break it into quarters - or monthly...



    It is an interesting problem......

  10. #10
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I would go the way Minty suggests: if you don't want to normalize the data, go with Excel. If this only is a one time operation, there is not much point in spending hours programming a solution in Access.

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

Similar Threads

  1. String to Reserved
    By monvani in forum Programming
    Replies: 9
    Last Post: 08-21-2015, 01:12 PM
  2. Bills of material
    By dgberry in forum Database Design
    Replies: 16
    Last Post: 04-14-2015, 01:21 PM
  3. VBA Reference material
    By Paintballlovr in forum Programming
    Replies: 3
    Last Post: 09-07-2013, 08:13 AM
  4. Reserved Error (|)
    By RGRAVLIN_BOS in forum Access
    Replies: 4
    Last Post: 06-14-2012, 07:09 AM
  5. Reserved word
    By squirrly in forum Access
    Replies: 6
    Last Post: 09-20-2011, 02:43 PM

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