Pivot Table to Show Blank Rows

[perezstudios]

Hello,

I know you all are probably going to laugh at this question but here it goes.

I have a PivotTable that I am trying to put together that will give me the following:

Types of Payments - Left
Accounts Where Payments came from - Top
Amount of Payments - Data

I want it to look like a spreadsheet where it will show me all the Types of Payments even though I don't have any data in there. Almost like an Excel Spreadsheet but I want it in Access. I was able to get the Columns to show up even though I had some blanks but now I need the Rows to show up.

Any help would be greatly appreciated!

Thanks,

Perez

 

[perezstudios]

I forgot to mention that I am using MS Access 2010!

Thanks again!

 

[JHB]

I want it to look like a spreadsheet where it will show me all the Types of Payments even though I don't have any data in there. Almost like an Excel Spreadsheet but I want it in Access. I was able to get the Columns to show up even though I had some blanks but now I need the Rows to show up.

Do you have data in you table(s), if not then put them in?
If yes, show you query string and some data, else post your database, (zip it).

 

[94Sport5sp]

Hi:

The pivot table can only show rows if there is something to show. If you have a blank payment type in your data then it will show up as a blank row but if you have two blank payment types then only one row will show up.

What are you trying to accomplish with blank data?

 

[perezstudios]

Guys,

I have attached a database to this thread! In the zip file I included a spreadsheet showing how I want my data to look and how it currently looks.

I am trying to create a report that looks similar to an Excel Spreadsheet with all my data in it.

Thanks again,

Perez

 

[JHB]

Open the only report in the attached database.

 

[perezstudios]

That works great, but now how would I recreate that? I want to do this same thing in a couple of other databases.

I see that you created a table with the items on it.

Thanks,

Perez

 

[JHB]

I see that you created a table with the items on it.

Exactly so!

 

[perezstudios]

JHB,

Are you going to say how you did this or should I look elsewhere?

Thanks,

Perez

 

[JHB]

Sorry - I thought you've figured it out as you mention I'm creating a table!!
Look in the report's open event and in the query.
The problem ("shit") is you are using a multiply field type for the different items (you are storing all the items in one field it is not appropriate and most programmer advise against it because that can cause problems, and you've faced one on them), therefore you need to create a table with the items, one item per row else you can't get all items showed in the cross tab query, only the items which have value would show up then.

Private Sub Report_Open(Cancel As Integer)
  Dim dbs As DAO.Database, rst As Recordset, splString() As String, x

  Set dbs = CurrentDb
  splString() = Split(dbs.TableDefs("Accounts").Fields("ItemsPaid").Properties("RowSource").Value, ";")
  Call SletTabel("ItemsPaidValue")
  dbs.Execute ("CREATE table ItemsPaidValue ([ItemsPaid] TEXT (25)) ")
  Set rst = dbs.OpenRecordset("ItemsPaidValue")
  For x = 0 To UBound(splString)
    rst.AddNew
    rst![ItemsPaid] = Mid(splString(x), 2, Len(splString(x)) - 2)
    rst.Update
  Next x
End Sub

 

[perezstudios]

Ok.

That helps a bit but now I am trying to recreate it.

I created another table and this time copied the code into the VB area. I updated the field names and table names and it doesn't work.

Anyone have a video or step by step process explaining this?

 

[JHB]

Maybe you should post what you have now, then we can see where problem (misunderstanding) occurs!