Results 1 to 8 of 8
  1. #1
    msAccessNoob is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    4

    Combine data/dynamic control


    Lets say i have a table with
    [Project][week1][week2].....[week50]

    My form auto calculates the dates for each week based off the input date and puts it in the correct box....

    Instead of having a weekly due report with 50 colums is there a way to display a report that has 3 colums

    [Project][date][dynamic info/date explanation]

    For the dynamic info i wanted it to display if it was from week1 or 2
    ..50

    So i want to combine all the dates from each column and only take the items that the dates fall in the current week. When it displays it it should be like

    Project1. Date within this week. Week3
    Project2. Date within this week. Week 40
    Project3. Date within this week. Week 43

    Etc

    Any assistance would be great

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    your table is designed wrong. In a relational db, it should be:
    [Project][week#][value]

    then you can query any week,any range.
    This can easily be fixed by pulling data from the existing data into the correct design.

  3. #3
    msAccessNoob is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    4
    Hmmm so is there a way to concat and use the AS to get what i want??

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Hmmm so is there a way to concat and use the AS to get what i want??
    doesn't look like it - you would need a criteria of 50 'OR's deep and 2 x 50 nested iifs (and I don't think you can have that many) and still not sure that is what you want

    probably not doable, you would need something like this
    Code:
    SELECT Project, iif(wk1 between thisdate and thatdate,wk1,iif(wk2 between thisdate and thatdate,wk2,iif(.... AS WeekDate, iif(wk1 between thisdate and thatdate,1,iif(wk2 between thisdate and thatdate,2,iif(.... AS WeekNo
    FROM myTable
    WHERE 
    wk1 between thisdate and thatdate OR
    wk2 between thisdate and thatdate OR
    wk3 between thisdate and thatdate OR
    wk4 between thisdate and thatdate OR...
    better to sort your data correctly as suggested by Ranman

    create a new table as he suggests

    then this query

    Code:
    INSERT INTO newtable (Project, WeekDate, Weekno)
    SELECT Project, wk1,1
    FROM oldtable
    WHERE wk1 is not null
    run it then change the bits in red to wk2 and rerun again - do for 50 times

    once done your data will be normalised and your query would simply be

    Code:
    SELECT *
    FROM newtable
    WHERE weekdate between thisdate and thatdate

  5. #5
    msAccessNoob is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    4
    Well I think I am going to not worry about where it came from and just go with the dates needed.

    I keep getting syntax errors when trying to union the data..

    [Project][Dates] I have a custom textbox as well named Dates that will hold the Week1....WeekN

    Form Load event I have..
    Dates.controlsource = "[Dates]"
    Me.RecordSource = "Select Project, Week1 as Dates From MyProjectsTable"

    When I run the report it works great and shows me all dates for Week1.

    When I try to add an union all it errors out..

    Me.RecordSource = "Select Project, Week1 as Dates From MyProjectsTable" & _
    "Union All" & _
    "Select Project, Week2 as Dates From MyProjectsTable"

    I also need it to only give me the dates that are within this week.. I tried Where Format(NOW(),"ww")=Format(Dates,"ww") and it didn't work.... it keeps popping up wanting to know what DATES is...with the box to type something in..

    THanks

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    When I try to add an union all it errors out..
    just a tip, this is not helpful for someone to help you, post the error message.

    In any event I can see you are missing spaces in your sql

    instead of assigning directly to your recordsource, assign it to a string instead

    sqlstr="SELECT....

    then on the following line put

    dubug.print sqlstr

    you will then see the resultant string in the immediate window

    then copy and paste this into the sql window of a new query and you will get more relevant error messages

    with regards

    Where Format(NOW(),"ww")=Format(Dates,"ww")
    Dates is an alias, you need to use Week1, Week2 etc

  7. #7
    msAccessNoob is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    4
    thank you ajax.. i used the sql wizard and then did the sql view to create/test my sql...it was working and when I moved it over to VBA I forgot a space.... Also one of the changes I did was change the as to AS (wasn't aware it was case sensitive, but I guess I learned something new).

    So I got it work..

    Thanks

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Access sql and vba are not case sensitive, so change as to AS will not make a difference. Only time it makes a case sensitive 'decision' is on binary comparisons, for example when specified the parameter in the instr function or using Option Compare Binary instead of Option Compare Database at the top of a module

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

Similar Threads

  1. Dynamic adjustment of a control
    By George in forum Modules
    Replies: 3
    Last Post: 05-20-2016, 08:09 AM
  2. Looking to combine data
    By brentagade in forum Access
    Replies: 3
    Last Post: 03-24-2016, 11:35 AM
  3. combine data
    By jozino in forum Queries
    Replies: 2
    Last Post: 04-06-2015, 01:31 PM
  4. Replies: 8
    Last Post: 07-30-2012, 04:43 PM
  5. Combine data
    By DSM1957 in forum Queries
    Replies: 1
    Last Post: 07-16-2012, 04:51 AM

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