hi all, access issue here

[dtdukes86]

oh i did manage the create a sub report based on the cross tab and thought that would work but when i linked the id numbers from sub report to main report i got a error, ive not seen before but is very annoying because i thought it was working . see photo

 

[dtdukes86]

ps #I tried to rename my column heading in design view of the cross tab with 1,2,3,4,5,6,7,8,9,10,11,12 representing the 12 weeks but then the sum data vanished , i tried this because i thought if i could fix the name of the headings i wouldn't loose the control in the sub report. once again stumped. i dont actually need to see the dates just the sum values in 12 weekly date order

 

[dtdukes86]

ive been looking at different ways for hours so i guess ill go back and redesign somehow.

thinking about what my customer has asked for...

we currently have a report "Report-CollectSheet" that shows 5 records per A4 page, customer loan information.

Each section shows things like loan date, payment due, any arrears, customer address and so on ,,,

the Subreport-PaymentHistory which has become the bain of my life was designed to show staff members a 12 weeks payment history,,

so! its the sum of payments received over the last 12 weeks shown in 12 weekly boxes, 1 box per week , simple ud think ,,

! i can get the data using qry but cant format it how the customer wants!

which is with dates of the week along the top and the sum of payments in for that customer underneath, well I can get this is a cross tab qry but i cant use that for a sub report because the date change. so there must be a better way.

somebody must know a better way to do this simple yet so hard task ,. as i said before this is a paid project so im happy to make a donation time spent for a working solution.

 

[The_Doc_Man]

There are ultimately only a few ways to do this. Several folks have discussed the idea that you would do better to move the data to Excel and then just pivot. If you are using a grid-like form / report for output, you COULD build something in which each text box gets filled programmatically using some VBA code, but if the table in question has more than 255 records in it, then when you pivot it you would have to generate more than 255 columns - and you can't do that in Access because of structural limits. Treating each place for text as a control, you would run into a limit of 768 controls on a form or in a report section, which also would be a limit. You can do this only for so big a table before you will be FORCED by those structural limits to stop. If you can get limits on how many columns will be required in your display, you can perhaps do this. If you cannot get limits imposed, you have painted yourself into a corner.

 

[dtdukes86]

There are ultimately only a few ways to do this. Several folks have discussed the idea that you would do better to move the data to Excel and then just pivot. If you are using a grid-like form / report for output, you COULD build something in which each text box gets filled programmatically using some VBA code, but if the table in question has more than 255 records in it, then when you pivot it you would have to generate more than 255 columns - and you can't do that in Access because of structural limits. Treating each place for text as a control, you would run into a limit of 768 controls on a form or in a report section, which also would be a limit. You can do this only for so big a table before you will be FORCED by those structural limits to stop. If you can get limits on how many columns will be required in your display, you can perhaps do this. If you cannot get limits imposed, you have painted yourself into a corner.

Hi Doc, the output sub report in this case is small there are 12 columns (weeks (Mondays date)) across the top running oldest on the left to newest on the right (in theory) and only one row to display the sum of payments in below it. so for this part at least the data is quite small. the table that feeds the 1st qry is big but i use a qry to grab the id i need before i do anything fancy with the data.

do u have any examples of how vba can fill in a text boxes arranged in a "grid like table" on a subreport using values from a qry. the 12 weeks are changing every week so i cant fix the date with a string i need them to change so maybe column headings can be, column1 top box = [weekcommencing] for example then ([weekcommencing] + 7) would give the 2 column top box. i can do this!! But then how do i get the corresponding sum cash total data from the qry into the lower half of my grid.

I'd need to select that from the table and place is nicely underneath. so difficult to explain i hope you still follow me. but i dont know how to select one field from a qry based on another field like this.

wow you certainly gave me food for thought, ! any thoughts on selecting the sum data based on the date.?

 

[dtdukes86]

maybe id have to make 12 small queries to find the result of each column in the grid? named them separately and refence each one in the report ? long winded ?

 

[Gasman]

Have you tried post #16?
I would put the data into Excel, then automate what you do with those 4 clicks.

Seems to be the easiest way to get what you want?

 

[dtdukes86]

Have you tried post #16?
I would put the data into Excel, then automate what you do with those 4 clicks.

Seems to be the easiest way to get what you want?

hiya, yes however this is for a sub report on a larger form , id then have to import the data back into access, which i did think would work however the following week the dates change therefore the sub report looks for dates that are no longer creating field name on the cross tab and that opens up another can of worms,

im working on something now where i have dates along the top and then 12 subreports based on 12 qrys. seems to be working but its big and messy

id like a way to reference the value on each qry from the report without having to make it a subreport first.

something like this in a textbox maybe "=qry1.[field] " seems simple enough bit I just get an error.

 

[dtdukes86]

so here is my current issue, ive sorted the dates along the top . thank you Doc

ive managed to link the data to a subreport using =[Query3 subreport].[Report]![PaymentMade] easily enough but im thinking is there a way to link to the qry itself rather than the sub report so save me creating 13 subreports and hiding them all,

(in the photo ive only linked to the sub report for date 19/7/2021 hence why the others are blank)

 

[dtdukes86]

hi all, thank you so much for all the help you guys gave and the advise . sadly the project i was working on has been cancelled by the buyer despite him having paid for a portion of the project they have decided to go for rebuilt package! shock horror. That said im now free to code something new if anybody wants any help or has any ideas they want building im happy to take on a new project as i enjoy the challenge.