By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,963 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,963 IT Pros & Developers. It's quick & easy.

Problem with report and sub form

100+
P: 418
One of the sub forms contain 3 rows of data but the report is pulling only data from the first row. The other two rows show up on later pages (separately). What could be the possible reasons?

Can anyone please help? Thanks.
Apr 17 '09 #1
Share this Question
Share on Google+
25 Replies


JustJim
Expert 100+
P: 407
@MNNovice
Did you mean "Report and sub-report" or "Form and sub-form"?
Apr 17 '09 #2

NeoPa
Expert Mod 15k+
P: 31,709
I think you need to rephrase the question M. This is not very clear.

There's no automatic connection between a form and a report, so if there is a link that connects them in this case you will need to explain what it is.
Apr 17 '09 #3

100+
P: 418
JustJim:

It seems I made a blunder. My report is based on various tables and no sub reports. However, one of the tables has several sub forms in it. So when I am trying to pull data on the report using all these tables, I am getting this weird report.

May be I should design the report and include sub reports based on these sub forms...Let's see if this resolves the problem.

Thanks.
Apr 17 '09 #4

100+
P: 418
NeoPa:

Please see my response to JustJim. I believe I made a blunder...Am I on the right track of what Jim and you tried to hint?

Thanks for the cue, I will try some other way of generating this report. Thanks a bunch. M
Apr 17 '09 #5

JustJim
Expert 100+
P: 407
@MNNovice
No, sorry, still not getting the picture. A table can not have sub forms in it. A report is like a look into your data and is based on the tables in your database, or perhaps on queries of those tables.

We are perfectly willing to help you with your problem, but you are going to have to be more clear so that we can understand what the problem is.
Apr 17 '09 #6

100+
P: 418
JustJim:

My apologies. I meant to say one of the forms that is based on a table has multiple sub form. Here is the structure of the form called frmNOGA:
tblNOGA has 3 sub forms
sfrmFundInfo (based on a table called tblFundInfo)
sfrmRevenue (based on a table called tblRevenue)
sfrmExpense(based on a table called tblExpense)
NOGAID is the primary key that connects all these tables.

I was able to create a report and inserted 3 sub reports based on the above sub forms. Since my sub forms are formatted to display in DATASHEET view, the report also shows it in the same format, ie., DATASHEET view.

I have a new problem. tblEmployee contains all project managers' names & titles. EmployeeID connects tblNOGA and tblExpense. But I couldn't pull their names on the report as it gives error message (something to do with outer join problem - which is beyond my understanding/knowledge of database)...

Thanks for your help.
Apr 17 '09 #7

JustJim
Expert 100+
P: 407
@MNNovice
Now we are getting somewhere! I'll leave off your "new problem" for the time being. If you have a table (tblNOGA), which has a primary key and three other tables (tblFundInfo, tblRevenue and tblExpense) each of which has a foreign key relating to the primary key in tblNOGA then we should be able to write a query which gets data from all of those tables and we can design a report based on that query. This would mean we will not need any sub-reports.

Your next step could be to try to write that query yourself or if you don't feel confident about that, post the fields you need on your report and the tables that these fields come from and we'll see how we go from there.

When we get past that to your "new problem" we are going to need more information than "something to do with outer join problem". We're pretty good here, but we are not mind readers. You will have to provide more details about any error messages you get.

Jim
Apr 17 '09 #8

100+
P: 418
Jim:

Thanks for your detailed note. I appreciate. I don't expect anyone to read my mind nor do I question your knowledge in this matter. My apologies for not being more detailed in my queries. Really sorry.

Here is the detailed structures for the tables. If I need to make changes to any of these structures, feel free to suggest. Because I want to do things correctly from the start.

I wanted these tables to display in table format but don't know how to get it done here in this forum. So please excuse me if my data does not look pretty.

I will work on designing a query as you suggested even though I am not good at it. I shall post questions as I move forward. Thanks for your suggestions and tips.

tblNOGA
Field Name Data Type Data from
NOGAID PK
GrantNo text
GrantTitle text
AmendmentNo text
RevisionNo text
TodayDt Date
AwardDt Date
StartDt Date
EndDt Date
FundSource text
ApportFY text
CFDANo text
AccountID number tblAccount
FundID number tblFund
OrgID number tblOrg
ProgramID number tblProgram
SubClassID number tblSubClass
ProjectID number tblProject
EmployeeID number tblEmployee
RevID number tblRevenue
ExpID number tblExpense


tblExpense
Field Name Data Type Data from
ExpID PK
NOGAID FK
ExpDescr text
EmployeeID number tblEmployee
AccountID number tblAccount
FundID number tblFund
OrgID number tblOrg
ProgramID number tblProgram
SubClassID number tblSubClass
ProjectID number tblProject
ExpBudgetAmt currency
ExpChangeAmt currency


tblRevenue
Field Name Data Type Data from
RevID PK
NOGAID FK
RevDescr text
AccountID number tblAccount
FundID number tblFund
OrgID number tblOrg
ProgramID number tblProgram
SubClassID number tblSubClass
ProjectID number tblProject
RevBudgetAmt currency
RevChangeAmt currency


Once again, many thanks.
Apr 17 '09 #9

JustJim
Expert 100+
P: 407
@MNNovice
MN, that looks like you are on to a winner. Create a new query in design view, put in those tables (I assume that tblFundInfo in on all this fun too), connect your Foreign Keys to your Primary Key, select the fields you want and run the query. If it retrieves the data you want, save the query then you can go on to designing a report based on that query. If you have trouble, come on back and we'll sort it out.

Looking good

Jim
Apr 17 '09 #10

100+
P: 418
Jim:

Thanks for the encouragement. Unfortunately my query didn't work out as I suspected. Here are some of the fields that didn't populate and show no data: AccountID, ProjectID anc ExpDescr. Nevertheless I tried to create a report based on the data from faulty query, I also notice additional problem. The report didn't display the Fund # which is based on the FundID from tblFund. Even though the query shows the funds.

Can I get some more help please?

Many thanks.
Apr 20 '09 #11

JustJim
Expert 100+
P: 407
@MNNovice
Hi
Sorry I missed your post overnight. Can you post the SQL for the query that isn't working? (Open query in design view, on the left of the toolbar click the view button and select SQL, copy the SQL and paste in a post here).

Jim
Apr 20 '09 #12

100+
P: 418
Jim:

Here is the SQL for the query.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblFundInfo.FundSourceID,
  2.        tblFundInfo.ApptYear,
  3.        tblFundInfo.FedSplitRatio,
  4.        tblFundInfo.LocalSplitRatio,
  5.        tblFundInfo.Total,
  6.        tblFundInfo.FTAAmount,
  7.        tblFundInfo.LocalMatch,
  8.        tblNOGA.NOGAID,
  9.        tblNOGA.GrantNo,
  10.        tblNOGA.GrantTitle,
  11.        tblRevenue.RevID,
  12.        tblRevenue.RevDescr,
  13.        tblRevenue.AccountID,
  14.        tblRevenue.FundID,
  15.        tblRevenue.OrgID,
  16.        tblRevenue.ProgramID,
  17.        tblRevenue.SubClassID,
  18.        tblRevenue.ProjectID,
  19.        tblRevenue.RevBudgetAmt,
  20.        tblRevenue.RevChangeAmt,
  21.        tblExpense.ExpID,
  22.        tblExpense.ExpDescr,
  23.        tblExpense.EmployeeID,
  24.        tblExpense.ExpBudgetAmt,
  25.        tblExpense.ExpChangeAmt
  26.  
  27. FROM ((tblNOGA LEFT JOIN tblFundInfo
  28.   ON   tblNOGA.NOGAID = tblFundInfo.NOGAID) LEFT JOIN tblExpense
  29.   ON   tblNOGA.NOGAID = tblExpense.NOGAID) LEFT JOIN tblRevenue
  30.   ON   tblNOGA.NOGAID = tblRevenue.NOGAID;
I suspect I didn't define the realtionships correctly. I have some other questions for you but let's solve this one first. Thanks for your help, I appreciate.
Apr 21 '09 #13

NeoPa
Expert Mod 15k+
P: 31,709
I said I was keeping an eye on things didn't I M ;)

Try to remember though, when posting SQL, it needs to be laid out clearly so that people can read it. I've rearranged this one for you, but remember if it's not easily legible (must be in tags and must fit reasonably well in the window too) then people may not go to the effort of reading it.

PS.
@MNNovice
From my recollection, they weren't too far off. I don't remember anything which might cause an issue with this. No access to it from here though as that's all at home.
Apr 21 '09 #14

100+
P: 418
NeoPa:

Thank you so much for keeping an eye on me and I sincerely appreciate you taking such good care of cleaning up my mess. May I ask how do you do this formatting? The other day I tried to put things in a table format but couldn't. And I know it's possible to do so because I saw in one of your posting. How do you do this?

I promise to be good and efficient when posting. Thanks.
Apr 21 '09 #15

NeoPa
Expert Mod 15k+
P: 31,709
@MNNovice
LOL. I like it M (although I don't think of myself as quite so schoolmasterish :D).

As for posting tabular data, there are three things to remember :
  1. Use the [ CODE ] tags. Anything displayed within these tags uses a non-proportional font. That means that an 'i' and a 'Z' both take up exactly the same width. This is very important for tabular display.
  2. Never use tabs. Always use spaces to fill the spaces between columns etc.
  3. Prepare the post in a text editor which also uses a non-proportional font (Notepad or TextPad are good examples). It can be done in more sophisticated editors like WordPad or Word even, but setting the font up is just an extra step, and they use characters sometimes that are not standard (Ellipses (...), Quotes ('), etc).
Apr 21 '09 #16

100+
P: 418
NeoPa:

This student is not so sharp - I need further clarifications.

I have access to Notepad and am willing to attempt preparing the document for next posting. Where do I get the TAG info? I only know the TAG for Bold and underline...

Expand|Select|Wrap|Line Numbers
  1.   Thanks 
M
Apr 21 '09 #17

NeoPa
Expert Mod 15k+
P: 31,709
It looks like you managed to find it M ;)

In case anyone else is interested though, the reply window has various buttons that add tags to your responses. The one for the [ CODE ] tags looks like a #.

If adding them manually, would put [ CODE ] at the start and [ /CODE ] at the end (no spaces).

NB. I added spaces in to ensure they did not get treated as proper tags. The proper tags have no spaces in them.
Apr 21 '09 #18

100+
P: 418
Jim / NeoPa:

I thought perhaps it will be helpful to know the purpose and design criteria for the DB I am trying to put together. Please see the attached. Thanks.
Apr 21 '09 #19

JustJim
Expert 100+
P: 407
OK, again I missed your post overnight (you must be on the other side of the planet) but I see that you've had a nice chat about codes!

OK, I've re-created what I could of your table structure and your query. The structure of your query seems to be fine, but of course I don't have any data in my tables.

Can you give me a few records of data for each of tblNOGA, tblExpense, tblRevenue and (the one I made up myself!) tblFundInfo.

Naturally, change the names, accounts, amounts etc to protect privacy, and you can PM them to me if you'd rather.

Jim
Apr 22 '09 #20

100+
P: 418
Jim:

Indeed I am on the other side of the world. I live in Minnesota, USA. We are 6 hours behind GMT.

I have sent you a PM with some details info.

Thanks for your help.

Jim, the report sample is attached. Please see post #19 for the other attachment. Thanks.
Apr 22 '09 #21

NeoPa
Expert Mod 15k+
P: 31,709
@JustJim
Along with just about everyone I expect Jim :D
Apr 22 '09 #22

JustJim
Expert 100+
P: 407
@JustJim
Good morning Antipodeans,

MN, the document that you sent me appears to be the report that you want to pull from your database. What I want is some data as it is in the databases. Names and amounts are not so important but I need to be on the same wavelength about which records in one table are related to which records in another table.

You have used a lot of foreign keys in each of these tables (possibly more than required, but I can't tell yet) so I need to have the same data that you are .

One way of getting this to me is to open a table and copy and paste some rows into a spreadsheet page, you can use another page in the same workbook for the next table.

Jim
Apr 22 '09 #23

NeoPa
Expert Mod 15k+
P: 31,709
M, I've sent you a PM about tonight. Please let me know as soon as you're able.
Apr 23 '09 #24

100+
P: 418
NeoPa:

I responded to your message, rather a bit late. Here is the updated DB for your review (when you can find some time).

Thanks for your help. I appreciate.
Apr 24 '09 #25

NeoPa
Expert Mod 15k+
P: 31,709
I'll download it when I get home M.
Apr 24 '09 #26

Post your reply

Sign in to post your reply or Sign up for a free account.