Forums: List of dates - Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

List of dates Where do I start Rate Topic: -----

#1 User is offline   jackheitzer@gmail.com 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 41
  • Joined: 27-December 12

Posted 19 March 2018 - 01:00 PM

Hi,

I would like to get some tips of you guys how to achieve the following

I have a list with this week's occupation of an orchestra.

Posted Image

SELECT
bezetting.ID1,
bezetting.dirigent,
bezetting.key1,
bezetting.keycomp,
bezetting.fluit,
bezetting.hoorn,
bezetting.gitaar,
bezetting.bas,
bezetting.drums,
bezetting.perc1,
bezetting.perc2,
bezetting.perc3,
`show`.ID,
`show`.aanvang,
`show`.datum,
month(datum) AS maand,
year(datum) AS jaar,
week(datum) AS week
FROM bezetting
RIGHT OUTER JOIN `show` ON `show`.ID = bezetting.ID1
ORDER BY `show`.datum, `show`.aanvang


I would like to have a list of dates for each individual person when they played that week.

Can anyone give me tips on how or where to start?


Best regards, Jack
0

#2 User is offline   admin 

  • Administrator
  • PipPipPip
  • Group: Admin
  • Posts: 15686
  • Joined: 03-February 03

Posted 20 March 2018 - 01:50 PM

Do you have an example of what kind of output you looking to get?
Best regards,
Sergey Kornilov
0

#3 User is offline   jackheitzer@gmail.com 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 41
  • Joined: 27-December 12

Posted 21 March 2018 - 08:54 AM

Hi Sergey,

Thank you very much for your time.

Here's an example of what I am looking for.

Posted Image

(This image should represent 2 pages)

The drop-down boxes at the top should be occupied by the names of the people who appear in the list (see original post).

From whatever name you choose from the drop-down list (lookupWizard), a list of dates show up.

Would this be possible with PHPrunner 9.8 or do I need the enterprise edition?

Thanks,

Jack
0

#4 User is offline   admin 

  • Administrator
  • PipPipPip
  • Group: Admin
  • Posts: 15686
  • Joined: 03-February 03

Posted 21 March 2018 - 04:15 PM

You need to write custom code for this kind of dropdown. Dropdown should list of musicians names and on selection should refresh the page showing a search results page for the query like this:
WHERE Gitaar='Jack' or Bas='Jack' or KeyComp='Jack' ...



You can check this for inspiration but it is going to be more complicated as you need to account for multiple fields:
https://xlinesoft.co..._for_search.htm
Best regards,
Sergey Kornilov
0

#5 User is offline   jackheitzer@gmail.com 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 41
  • Joined: 27-December 12

Posted 22 March 2018 - 10:35 AM

View Postadmin, on 21 March 2018 - 04:15 PM, said:

You need to write custom code for this kind of dropdown. Dropdown should list of musicians names and on selection should refresh the page showing a search results page for the query like this:
WHERE Gitaar='Jack' or Bas='Jack' or KeyComp='Jack' ...



You can check this for inspiration but it is going to be more complicated as you need to account for multiple fields:
https://xlinesoft.co..._for_search.htm



Thanks!
0

#6 User is offline   jadach 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 382
  • Joined: 26-April 08

Posted 22 March 2018 - 11:11 AM

Why not just use a report?
-jerry
0

#7 User is offline   YCH 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 71
  • Joined: 06-September 11

Posted 22 March 2018 - 01:17 PM

Could a pivot table query be of any help ?
0

#8 User is offline   jackheitzer@gmail.com 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 41
  • Joined: 27-December 12

Posted 22 March 2018 - 01:49 PM

View Postjadach, on 22 March 2018 - 11:11 AM, said:

Why not just use a report?


I've tried a report but I can only process one column at a time. Or do you know of other options to get the job done?

Thanks, Jack
0

#9 User is offline   jackheitzer@gmail.com 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 41
  • Joined: 27-December 12

Posted 22 March 2018 - 01:55 PM

View PostYCH, on 22 March 2018 - 01:17 PM, said:

Could a pivot table query be of any help ?



How would I do that?

Thanks, Jack
0

#10 User is offline   YCH 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 71
  • Joined: 06-September 11

Posted 22 March 2018 - 04:33 PM

View Postjackheitzer@gmail.com, on 22 March 2018 - 02:55 PM, said:

View PostYCH, on 22 March 2018 - 01:17 PM, said:

Could a pivot table query be of any help ?



How would I do that?

Thanks, Jack


I am not so sure a pivot table query is still the right way.

But you could make a "view" with a "select ...union all... " query as data source like this:

***************
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.dirigent as persoon
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.key1
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.keycomp
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.gitaar
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.bas
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.hoorn
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.fluit
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.drums
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc1
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc2
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc3
FROM bezetting

;

************

I did not test this.

***********
You would end up with this select list :
persoon Id datum aanvang

arthur 1 donderdag 22 maart 2018 20:00 arthur 2 woensdag 21 maart 2018 20:00

arthur 4 vrijdag 23 maart 2018 20:00

arthur 5 zaterdag 24 maart 2018 15:00

... guillaume 1 donderdag 22 maart 2018 20:00

...

hilde 3 donderdag 22 maart 2018 20:00

hilde 4 vrijdag 23 maart 2018 20:00

...jack 4 vrijdag 23 maart 2018 20:00

jack 3 donderdag 22 maart 2018 20:00

jack 2 woensdag 21 maart 2018 20:00

jack 1 donderdag 22 maart 2018 20:00

jack 5 zaterdag 24 maart 2018 15:00

...


jan 5 zaterdag 24 maart 2018 15:00

...


joeke 4 vrijdag 23 maart 2018 20:00

...


johannes 2 woensdag 21 maart 2018 20:00

johannes 1 donderdag 22 maart 2018 20:00

...
mark 5 zaterdag 24 maart 2018 15:00

mark 3 donderdag 22 maart 2018 20:00

mark 2 woensdag 21 maart 2018 20:00

...
rené 3 donderdag 22 maart 2018 20:00

rené 1 donderdag 22 maart 2018 20:00

...


rogier 4 vrijdag 23 maart 2018 20:00

rogier 3 donderdag 22 maart 2018 20:00

...


rosite 2 woensdag 21 maart 2018 20:00

rosite 5 zaterdag 24 maart 2018 15:00

*******

this could be the view data source for your page.






0

#11 User is offline   jackheitzer@gmail.com 

  • Advanced Member
  • PipPipPip
  • Group: Members
  • Posts: 41
  • Joined: 27-December 12

Posted 26 March 2018 - 12:42 PM

View PostYCH, on 22 March 2018 - 04:33 PM, said:

View Postjackheitzer@gmail.com, on 22 March 2018 - 02:55 PM, said:

View PostYCH, on 22 March 2018 - 01:17 PM, said:

Could a pivot table query be of any help ?



How would I do that?

Thanks, Jack


I am not so sure a pivot table query is still the right way.

But you could make a "view" with a "select ...union all... " query as data source like this:

***************
SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.dirigent as persoon
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.key1
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.keycomp
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.gitaar
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.bas
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.hoorn
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.fluit
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.drums
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc1
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc2
FROM bezetting
union all

SELECT bezetting.Id, bezetting.datum, bezetting.aanvang, bezetting.perc3
FROM bezetting

;

************

I did not test this.

***********
You would end up with this select list :
persoon Id datum aanvang

arthur 1 donderdag 22 maart 2018 20:00 arthur 2 woensdag 21 maart 2018 20:00

arthur 4 vrijdag 23 maart 2018 20:00

arthur 5 zaterdag 24 maart 2018 15:00

... guillaume 1 donderdag 22 maart 2018 20:00

...

hilde 3 donderdag 22 maart 2018 20:00

hilde 4 vrijdag 23 maart 2018 20:00

...jack 4 vrijdag 23 maart 2018 20:00

jack 3 donderdag 22 maart 2018 20:00

jack 2 woensdag 21 maart 2018 20:00

jack 1 donderdag 22 maart 2018 20:00

jack 5 zaterdag 24 maart 2018 15:00

...


jan 5 zaterdag 24 maart 2018 15:00

...


joeke 4 vrijdag 23 maart 2018 20:00

...


johannes 2 woensdag 21 maart 2018 20:00

johannes 1 donderdag 22 maart 2018 20:00

...
mark 5 zaterdag 24 maart 2018 15:00

mark 3 donderdag 22 maart 2018 20:00

mark 2 woensdag 21 maart 2018 20:00

...
rené 3 donderdag 22 maart 2018 20:00

rené 1 donderdag 22 maart 2018 20:00

...


rogier 4 vrijdag 23 maart 2018 20:00

rogier 3 donderdag 22 maart 2018 20:00

...


rosite 2 woensdag 21 maart 2018 20:00

rosite 5 zaterdag 24 maart 2018 15:00

*******

this could be the view data source for your page.



Hi,

I'm looking into this and it seems to help a lot. Thanks!

Jack
0

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic