Help creating crosstab query

Hi all,

I'm probably going about this the wrong way, but I want to know if it
is possible to use a crosstab query to display what I need.

Scenario:  People pay into a lotto syndicate each fortnight.  The
amount is $5.  So in effect, they pay today and they next need to pay
on 11th April 07.

However, some rich people want to pay more than one period at a time,
and obviously some people fall behind in their payments.  So we could
have Rich Joe who has now paid until 25th April by paying $10, and
Poor Mary who pays $20 to catch up to today's deadline, and she next
needs to pay on 11th April.

This is the sort of stuff I need to see in the crosstab:

                        28-Mar-07  | 11-Apr-07  |  25-Apr-07

Joe                           5.00  |         5.00  |
Mary                         5.00  |                 |

Basically what I want it to do is to add $5 each fortnight, depending
on how much they enter.   It would be simple if they all paid $5 on
the due date, but they don't!

Is anything like this possible in a query?   Or should I use code on
the form to split any monies paid into $5 lots, and add it on a
fortnightly basis?

Any help greatly appreciated!

DubboPete

0
DubboPete
3/28/2007 1:50:27 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
644 Views

Similar Articles

[PageSpeed] 48

The problem can be seen as being the table design, no the crosstab, at 
least, to me :-)  And not its static aspect, but how you 'manage' it, how it 
evolves, how you correct mistake (anyone does that stuff, so it should be 
part of the plan), and so on.

Sure, the easy approach, easy for me at least, would be to have one record 
per week and if Mary has not contributed for a given week, there is no 
record mentioning her, for that week_date_stamp:


Employee,    DateStamp        ' fields name


and if you added Joe, by error, for a given week, then, correct the mistake 
by deleting the record (or add it if he did pay but no record was ever 
inserted).  If Joe pay for the next 3 weeks, insert 3 records, with the 
proper weeks stamp. If some employee can have more than one 'participation', 
for a given week, add a third field, Amount. THe crosstab is thus:


TRANSFORM Nz(LAST(Amount),0)
SELECT Employee
FROM tableName
GROUP BY Employee
PIVOT DateStamp


or, if you don't have the field amount,

TRANSFORM  Format(5*Nz(COUNT(*), 0), "currency")
SELECT Employee
FROM tableName
GROUP BY Employee
PIVOT DateStamp



as example.


Hoping it may help,
Vanderghast, Access MVP



"DubboPete" <osnabruc@tpg.com.au> wrote in message 
news:1175046627.013350.271570@p77g2000hsh.googlegroups.com...
> Hi all,
>
> I'm probably going about this the wrong way, but I want to know if it
> is possible to use a crosstab query to display what I need.
>
> Scenario:  People pay into a lotto syndicate each fortnight.  The
> amount is $5.  So in effect, they pay today and they next need to pay
> on 11th April 07.
>
> However, some rich people want to pay more than one period at a time,
> and obviously some people fall behind in their payments.  So we could
> have Rich Joe who has now paid until 25th April by paying $10, and
> Poor Mary who pays $20 to catch up to today's deadline, and she next
> needs to pay on 11th April.
>
> This is the sort of stuff I need to see in the crosstab:
>
>                        28-Mar-07  | 11-Apr-07  |  25-Apr-07
>
> Joe                           5.00  |         5.00  |
> Mary                         5.00  |                 |
>
> Basically what I want it to do is to add $5 each fortnight, depending
> on how much they enter.   It would be simple if they all paid $5 on
> the due date, but they don't!
>
> Is anything like this possible in a query?   Or should I use code on
> the form to split any monies paid into $5 lots, and add it on a
> fortnightly basis?
>
> Any help greatly appreciated!
>
> DubboPete
> 


0
Michel
3/28/2007 12:39:50 PM
Reply:

Similar Artilces:

Need help to call Compact & Repair Database command from a office VB script
I have a VB script in Excel that runs something like SET db = opendatabase ("Filename") db.querydefs ("Run query 1" etc... What I'm doing is running a query to delete the data in the table, drop the indexes, load the data and then rebuild the indexes. I'd really like to throw in a compact and repair database as well to avoid the 2 GB data limit and the subsequent "invalid parameter limit". Can someone guide me how to run the compact database command please? Thanks. Public Sub CompactDatabase() On Error Resume Next dao.DBEngine.Compac...

excel pivot table with sql query and visual basic macros
I have a pivot table that runs off a sql query, it has macros and parameters built into the report. It worked until the client updated their server and db software. When debugging this, on the module under the closedb it stops here: Sub CloseDB() 'IF CONNECTION IS OPEN IT NEEDS TO BE CLOSED SO AN ERROR IS NOT THROWN If rsData.State = 1 Then rsData.Close Set rsData = Nothing End If cnData.Close Set cnData = Nothing Set Cmd = Nothing End Sub Thanks ...

Formula In A Query
Hi I have a query in which I am trying to create a formula to check if the date in a record is in the same month as the current date, if this is true to return the months name, if not to return another value. The formula does not fall over but only returns the OR value. The OR value is meaningless at the moment as I am just seeing if the formula is actually doing something Could someone have a look at this MonthNo: IIf([Date SubmittedtoLab]=Month(Date()),MonthName(Month([Date SubmittedtoLab])),5) Thanks Richard Hi Richard, You can use the Immediate Window (open with <Ctrl><...

Excel 2002 Create Workbook (merge/reformat)?
I have 12 seperate spreadsheets I want to combine into 1 workbook (12 tabs). Any other alternative ways to do this other than copy and paste? Need to reformat "most" of the worksheets, I assume this can be done if all 12 tabs are selected. I may have to do this several times otherwise I would just copy and paste. Each spreadsheet has an average of 3k-4k transactions. You can open the workbook, ctrl-click on the worksheet tab and drag that worksheet into another workbook. (Then close that "sending" workbook and open the next...) ~Dave~ wrote: > > I have 1...

How can I pre-filter a table that is used in a query in a live dat
How can I pre-filter a table that is used in a query in a live database? I have about 47 queries (SQL text in a memo field in a table) that all reference a few tables in the same database. I would like to limit the table’s data instead of re-writing all the queries to include further criteria. The queries are very complex and it is not desirable to re-write the queries and use parameters. This database is live on an ASP.NET web page and there may be multiple people viewing the reports which are using these queries. Since there are multiple users I can not simply copy the tables into ...

PopUp Form Help
I'm trying to create a pop-up form to allow users to enter data to table that main form is based on. Due to size of main form and because pop-up fields will only be used per certain entries on main form, I want to use a pop-up form to enter needed data. On the main form, I have the fields listed below. For example, if user enters "TypeOne" as a PType and then enters Model, AfterUpdate to Model field a pop-up form opens with fields for UnitGroupName and UnitModelName. I would like the UnitGroupName to be populated with "All TypeOne Products" when the po...

Create a Line Chart
I am having great difficulty trying to create a line chart in Access 2007. I have a report with the following fields: Staff_Component 1QTR 2QTR 3QTR 4QTR Operations 10 28 45 33 Can someone please explain to me how I can create a line chart? Thanks and Happy Holidays! *Glen* The report fields don't mean anything. You can generally add a chart control to your report and use the wizard to set up your chart properties. I expect you will need to update the Row Source property since the wizard often clobbers it. T...

How to create a rule which triggers when a condition is NOT met?
.... for instance, in OLExpress and Pegasus one can create a rule with the symbol "@" in the conditions and request the rule to "move to the Junk email folder" all mail which do NOT meet the condition. I can't seem to find this facility anywhere in MS Outlook. Thanks for your help in advance and apologies if this question has already been asked and answered elsewhere (if it *has* I would be grateful for the link). Regards, russel You can achieve this by creating a rule which works on all mail (by not having any selection criteria) then use "except" th...

Creating a query out of another query
Hi Guys, I have a query looking like that: field1 field2 field3 field4 field5 field6 Data 1 Test 6 Poly 8 Data 5 Poly 10 I need a query from this query like this: Field 1 Field 2 Data 1 Test 6 Poly 8 Data 5 Poly 10 I'll really appreciate if someone can help me with this one Thanks This should do it -- SELECT Field1 AS Field_1, Field2 AS Field_2 FROM YourTable WHERE Field1 ...

How do I create a 46 page member directory?
I was wondering if publisher can create a membership directory that would be 46 pages in length? The front of the handbook would be the names/adresses and so forth and the back part would be images of each businesses business card. Is publisher the right software for this? I was volunteered for this job, so any help would be most helpful! Thanks, Bryan I'd use Publisher if I was doing that job. -- JoAnn Paules MVP Microsoft [Publisher] "Bryan" <Bryan @discussions.microsoft.com> wrote in message news:D94BC8CE-1B55-443E-9925-65AC606F5A52@microsoft.com... >I wa...

Error message creating install package
I am trying create an install package but allways I get error message after pressing OK at the final phase. The message is: "The destination install subfolder name cannot contain any of the following characters: /:*?"<>|+,;=[]" My subfolder's name is Family. Why this message? -- Have a Nice Day! ...

Help monthly sales by person
Need formula to figure amount of sales per person in a given month. columns currently are: Inv#, Date, Sale, Net $, Salesman # Need formula for monthly sales per Salesman # Thank you -- Htoomuc Posted from - http://www.officehelp.i With your headers in Row1, from Columns A to E, And the datalist from A2 to E100, Enter the month and year to lookup in F1 (mm/yy), And the salesman's # in F2, And try this: =SUMPRODUCT((TEXT(B2:B100,"mm/yy")=TEXT(F1,"mm/yy"))*(E2:E100=F2)*D2:D100) -- HTH, RD --------------------------------------------------------------------------...

please help #3
In Money 2003, when I receive payment on an invoice, it automatically records it to my register. Is there any way around this? The reason is that I like to make one large deposit, and I want this large deposit to show in my register, not the individual ones. Thanks!! .. ...

Multiple database files created
I have an Access 2007 database that resides on our company's server. The database tracks incoming and outgoing shipments and is used by several employees throughout the day. I've noticed that in the folder where the database resides several additional database files are created throughout the day with names like database.mdb, database1.mdb, database2.mdb, etc. Can anyone tell me why this is happening? The database was created with Access 2007 using the 2003 mdb file format, as most of the end users have Office 2003 applications. Ken Warthen kenwarthen@gmail.com 1. Turn off...

my file still exists but is blank please help
I had a virus completely delete a file in my microsoft word 2000. It's old and I don't know what to do. The file name is still there but the text is blank, and I've tried several recovery progrms that haven't retrived it. Was hoping someone knew if word has its own back to retrieve things from and how to use it If the contents have been overwritten (and it certainly seems as if that is the case), there isn't anything you can do, I'm afraid. -- Stefan Blom Microsoft Word MVP "hanna" <hanna@discussions.microsoft.com> wrote in messa...

PLEASE HELP: old Money 1998 and missing section
I was checking something I archived from 1999 and suddenly my whole bank account disappeared and every transaction that I had posted in the last 1 1/2 years went with it. I have no idea where they went; would anyone guess?? In microsoft.public.money, JA wrote: >I was checking something I archived from 1999 and suddenly >my whole bank account disappeared and every transaction >that I had posted in the last 1 1/2 years went with it. I >have no idea where they went; would anyone guess?? I would suspect that you specified an archive date that was after the last transaction...

Creating a template for a cross-functional flowchart
Hi, I use Visio 2003. I am trying to create a template for a cross-functional flowchart. 1) The title bar needs to be 0.5in in height, containing the logo, revision number, approver name, etc. 2) This is followed by a blank frame of height 0.25in. 3) Then come 5 bands. The user should be able to add or delete bands as required. I created a cross-functional flowchart with 5 bands (There is a title bar, a blank frame, and 5 bands). I then used the shapesheet to increase the height of the title bar. Then I added the logo and other details to the title bar and saved this as a template. Af...

query help 01-08-08
My main table is called tblMain and this has an abundance of information in there. Each record has an ID called MainID. I need to do a 'search type query' on 4 fields . I have a primary business unit which each record must fill in (PrimaryBU). I then have another 3 related business unit fields (RelatedBU1, RelatedBU2, RelatedBU3). All 4 of these fields are linked to a lookup table tblBusinessUnit. I need to write a query that will prompt the user to type a business unit (I know how to do this part) and will then show any record which has that business unit in any of those 4 fie...

Help with econnect trying to create a contract for GP
Hi all, I am trying to use the serializer and create a contract/project and budget line items, i was wondering if anyone has some examples. When i use the serializer with contract, it drops off the pstat value in the xml document and then recive the error that a variable is empty. Thanks all ...

Help with a Loop
How do I write a macro which loops this ? Sheets("Data1").Select Range("C5").Select Selection.Copy Sheets("ActivityData").Select Range("C4").Select ActiveSheet.Paste Link:=True Sheets("Data2").Select Range("C5").Select Application.CutCopyMode = False Selection.Copy Sheets("ActivityData").Select Range("C5").Select ActiveSheet.Paste Link:=True Sheets("Data3").Select Range("C5").Select Application.CutCopyMode = False Selecti...

Create graph to compare spending (Money Plus Deluxe 2008)
I am trying to compare spending in just one category (groceries) between two different periods, like month by month, this month to last month, or the first 10 months of last year to the first 10 months of last year. I can't find how to do this. I've been able to do it with just a report, but I'd like to look at in graph form, preferably a bar graph. Can anyone help? In microsoft.public.money, Rick wrote: >I am trying to compare spending in just one category (groceries) between two >different periods, like month by month, this month to last month, or the >first 10...

Create contact with VBScript
This is a multi-part message in MIME format. ------=_NextPart_000_096D_01C3A874.FD3C0E30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have the following code to create a contact in a Public Folder. Code = was working fine until we upgraded the box it was running on to Win2k. = Hasn't run since. I'm not blaming Win2k, just note-ing that the = platform did change. Box has Outlook 2000 pre-SR1 installed and CDO.DLL = version 5.5.2653.12. The error is reported on the line with Items.Add (olContactItem) and = says: "Mi...

Help with "current client" list
Hi All, I've got a table named tblClient that has a ClientID as PK. I've got a table named tblTransactions that includes a FK of ClientID and some other controls one of which is DateOfTrans that is the date the transactions happened. I need a query, one row per client, of anyone that has had a transaction since 1/1/2006. I've tried it on my own and I get something like 6 lines for John Smith, 11 lines for Harry Wilson, etc...and these lines correspond to the 6 transactions for John and 11 for Harry since 1/1/2006. Not what I need. I need just one line per cl...

How to create a simple Stacked Column chart?
I'm pulling my hair out over what I thought would have been a simple task: I have an Excel 2003 pivot table and chart. On the Chart tab, I drag a field to the Category axis and Page. Then I drag 3 numeric fields to the Data area. I set the Chart Type to Stacked Column. The problem: the 3 numeric fields still display as 3 separate bars/columns. So the question is why doesn't Excel stack them? I have seen numerous similiar posts, but no real understandable answer (at least not to a newbie at Excel charting and pivot tables). Thanks!!! Hi, The number of items in the stack will ...

To create a stacked column chart and group the stacked bars togeth
I have a column chart. The columns are stacked. I want to create groups of stacked columns and put them close together. If you stagger your data, you can create side-by-side stacked columns. Bernard Liengme has an example and instructions on his site: http://www.stfx.ca/people/bliengme/ExcelTips/Columns.htm and Jon Peltier has links to other sites with information: http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html Jacqueline wrote: > I have a column chart. The columns are stacked. I want to create groups of > stacked columns and put them close together. -- Deb...