UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Please Help, Crosstab Report, Access 2003    
 
   
mr.siro
post Feb 9 2018, 11:22 PM
Post#1



Posts: 72
Joined: 27-January 18



i have table


i want to report


somebody help me.
Go to the top of the page
 
GroverParkGeorge
post Feb 10 2018, 07:09 AM
Post#2


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

There is a Crosstab Query Wizard which will guide you through the procedure to create a crosstab query that returns the results you want.

However, this is a screen shot from Excel, not from Access, is it not? The two-level header and the little green triangles reveal that.

So, if you want to put the results into a similar looking report, you'll need to create the crosstab query and a report that uses it.

Either that, or you could try exporting the resulting crosstab to Excel for final layout.
Go to the top of the page
 
mr.siro
post Feb 10 2018, 09:43 PM
Post#3



Posts: 72
Joined: 27-January 18



Yes, it's excel. But i want to report access exactly the same. I have table in screen shot1, and want report exactly sreen shot 2. It's possible?Can you help me that.
This post has been edited by mr.siro: Feb 10 2018, 09:44 PM
Attached File(s)
Attached File  db4.zip ( 9.33K )Number of downloads: 4
 
Go to the top of the page
 
RJD
post Feb 11 2018, 08:30 AM
Post#4


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but IF you know ahead of time which months/years you want to calculate/display, and IF you are using the [startday] to determine the appropriate column in which to put the totals, then this should do what you want. It uses only the months/year you specified in your table, but you can add others as necessary ...

CODE
SELECT
Table1.id,
Table1.[idname],
Sum(IIf(Right([startday],4) ="2018" And Mid([startday],4,2) ="01" And [productname]="computer",[quantity],0)) AS 201801c,
Sum(IIf(Right([startday],4) ="2018" And Mid([startday],4,2) ="01" And [productname]="printer",[quantity],0)) AS 201801p,
Sum(IIf(Right([startday],4) ="2018" And Mid([startday],4,2) ="01" And [productname]="mouse",[quantity],0)) AS 201801m,
Sum(IIf(Right([startday],4) ="2018" And Mid([startday],4,2) ="02" And [productname]="computer",[quantity],0)) AS 201802c,
Sum(IIf(Right([startday],4) ="2018" And Mid([startday],4,2) ="02" And [productname]="printer",[quantity],0)) AS 201802p,
Sum(IIf(Right([startday],4) ="2018" And Mid([startday],4,2) ="02" And [productname]="mouse",[quantity],0)) AS 201802m
FROM Table1
GROUP BY Table1.id, Table1.[idname]
ORDER BY Table1.id;


Note that I changed [name] to [idname] since "name" is a reserved word and can cause problems in some cases. And also note that you made the dates text, not dates - and I left them that way.

However, if the date range could be variable each time, if you have missing data in some months (as you do now, but applied to a crosstab), and/or if you want to somehow use both the start and end days to figure the contents of individual months, then you have some difficulties in creating the query you want, and especially in displaying the results in a report.

HTH
Joe
Attached File(s)
Attached File  db4_Rev1.zip ( 18.25K )Number of downloads: 4
 
Go to the top of the page
 
orange999
post Feb 11 2018, 09:28 AM
Post#5



Posts: 1,856
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


mr.siro,

Joe has answered your direct question.

For reference,
-every table should have a Primary Key
-values, amounts, counts...anything you intend to do arithmetic with ... should be a numeric data type
-dates should be Date/Time datatype

Good luck with Access.
Go to the top of the page
 
RJD
post Feb 11 2018, 05:11 PM
Post#6


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


Thanks, Orange, for adding that. All excellent points I went right by.

Regards,
Joe
Go to the top of the page
 
mr.siro
post Feb 11 2018, 08:11 PM
Post#7



Posts: 72
Joined: 27-January 18



Great, thanks you very much, very happy. But, I have one more left. I have value in the sceen shot1, stardate 31/01/2018, endday 02/02/2018. I want to report in sceenshot2, One computer at a day. One computer at 31/01/2018 (01/2108), two computer at 01/02/2018 and 02/02/2018 (02/2018)



The same for year, example: 02 computer with startday 30/12/2018 (12/2018), endday 01/01/2019 (01/2019) => 01 computer at 12/2018, 01 computer at 01/2019
This post has been edited by mr.siro: Feb 11 2018, 09:05 PM
Go to the top of the page
 
RAZMaddaz
post Feb 11 2018, 08:51 PM
Post#8


UtterAccess VIP
Posts: 9,564
Joined: 23-May 05
From: Bethesda, MD USA


PMFJI,

If you remove the ID Field from the Crosstab, then the Printer will only show once.

The reason it is showing twice, is because you have ID = 1 and ID = 2

RAZMaddaz

Here is an example, using your data.
This post has been edited by RAZMaddaz: Feb 11 2018, 09:08 PM
Attached File(s)
Attached File  db4_New.zip ( 10.91K )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Feb 11 2018, 10:38 PM
Post#9


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


mr.siro: How would Access know how you want to distribute the quantity across months? The quantities seem to be total across the time span, and you seem to want the quantities as 1 for each date in the date span. Is this correct? is this done by calendar days, or work days, or ...? If distributed, then more work has to be done to distribute the quantities correctly - this will not be done automatically. Can you give us the algorithm/instructions for distribution? Is the quantity the total use-days of the item? It this the same for all items?

HTH
Joe
Go to the top of the page
 
mr.siro
post Feb 12 2018, 02:36 AM
Post#10



Posts: 72
Joined: 27-January 18



01 kind of product a day is a rule, always be that.
Example: 03 computer, startday: 01/01/2018 => endday: 03/01/2018
02 printer, startday: 01/01/2018 => endday: 02/01/2018
User can type the quantity printer, computer or mouse and startday in form, but endday is startday + quantity (I have resolved this)
Now, i want to report as screenshot2.
This post has been edited by mr.siro: Feb 12 2018, 03:10 AM
Go to the top of the page
 
RJD
post Feb 12 2018, 10:34 AM
Post#11


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


Hi again: Given your new information that the quantities are spread out over the indicated date range, your data are not currently configured to do what you want. You will have to reconfigure your data to accomplish the display you described. This reconfiguration can be done within Access in at least two ways: 1) with queries, linking your current table to a new table of dates, with criteria to produce multiple records per current record or 2) with VBA to create a new table of records, expanded according to the date ranges of each record.

In other words, you will have to split the records up into their component date parts, so that later you can recombine them in month groups. Access does not do this for you automatically.

You will also have to deal with the facts that you are storing the dates as text and using European date formats rather than the Access method of preferring US date formats.

The resulting query or table can then be used to create the result you want, using the method i previously posted.

You should explore these methods and see which is best suited to your skills and desires, then give one of them a try.

HTH
Joe
Go to the top of the page
 
mr.siro
post Feb 12 2018, 10:52 AM
Post#12



Posts: 72
Joined: 27-January 18



Hello. i'm so happy with your help. Can you create a demo.



This post has been edited by mr.siro: Feb 12 2018, 11:15 AM
Attached File(s)
Attached File  db4.zip ( 8.98K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Feb 12 2018, 10:55 AM
Post#13


UtterAccess VIP
Posts: 8,809
Joined: 25-October 10
From: Gulf South USA


QUOTE
Can you create a demo.

Rather than having me do it for you, you should try it first and see how it goes. Pick a method and try. Then ask for help when you get stuck, showing what you have done so far. This will be a good learning experience for you.

HTH
Joe
Go to the top of the page
 
GroverParkGeorge
post Feb 12 2018, 11:07 AM
Post#14


UA Admin
Posts: 33,536
Joined: 20-June 02
From: Newcastle, WA


PMFJI:

Our goal here is not to do the work for people. Our goal is to help people learn how to use Access the most effective way possible.

That means, among other things, that you should take the time to study the materials offered in our Newcomers Reading List and then try to apply them to YOUR project.

Joe's already made that suggestion and I urge you to take a break from the current situation while you do that.

Having a valid table design makes many things much easier and more straightforward. It's going to be well worth the effort.

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 10:08 PM