Strange Form design (1 Viewer)

LjushaMisha

Registered User.
Local time
Today, 01:04
Joined
Mar 10, 2017
Messages
55
Hello,

Attached I send FormProblem.docx file with my problem.

From my Access database I get table (which is top page spreadsheet like)

From this table (query) I'd like to make a form which would be like the
bottom page spreadsheet

With my knowledge I'm not able to find solution althoug I'm trying this for last 14 days :banghead::banghead::banghead:

Pls help
best regards
 

Attachments

  • FormProblem.docx
    14.5 KB · Views: 31

isladogs

MVP / VIP
Local time
Today, 00:04
Joined
Jan 14, 2017
Messages
18,186
You should be able to achieve this with a CROSSTAB query.
There is a wizard to help you do this
 
Last edited:

LjushaMisha

Registered User.
Local time
Today, 01:04
Joined
Mar 10, 2017
Messages
55
Hi my SAVIOR
At first glace it seems that it Works. At least at the level of "data distribution" to records and fields.
Now I have to create I form and see if it Works as I wished.
Will send results as soon as I try
Thanks again
 

isladogs

MVP / VIP
Local time
Today, 00:04
Joined
Jan 14, 2017
Messages
18,186
You're welcome.
Crosstab queries are a very useful tool for converting normalised data into a 'spreadsheet type' format for display purposes

If you are using Access 2010 or earlier, there is another feature called a pivot table which is even more powerful. However, despite widespread objections that was dropped in A2013 and has never been reinstated
 

LjushaMisha

Registered User.
Local time
Today, 01:04
Joined
Mar 10, 2017
Messages
55
Yes, as newer the things are, less convinient they are (they don't bring enough money thru publicity)

Nevertheless,

As my crosstab query changes with everyday or with evry order I have problem with "dinamically design" of my form. What I mean with that:
Names of controls are changed every time I run crossquery and also their number varies with each change.

In one way I can use crosstable query for displaying correct data and as base it is enough. But my wish is to put some conditional formatting, for example difference between ordered quantity and produced quantity(different back color), finished items (different fore color), etc.

If not to complicated I'd like to try some.

Regards
 

isladogs

MVP / VIP
Local time
Today, 00:04
Joined
Jan 14, 2017
Messages
18,186
In crosstab queries, columns are normally only created where there is data.
However you can have fixed column headers e.g. Jan/Feb/Mar etc so all months are shown even where no data exists.
Where the column headers themselves vary, you can use code to create dynamic column headers. I have numerous examples of that online including http://www.mendipdatasystems.co.uk/extended-file-properties/4594398115

You can also use conditional formatting in reports based on crosstab queries. For example, see two of the screenshots at http://www.mendipdatasystems.co.uk/school-data-analyser/4584605482
 

Users who are viewing this thread

Top Bottom