Newbie Questions

Re: Missing query results when two tables are joined

kminor — Thu, 11 Jun 2009 15:42:57 GMT

Hmm... It looks like they're linked correctly:

shape

{select [RequestID], [RequestDate], [RequestRequiredDate], ..., [RequestMonth], [RequestYear], [Scratch]
from [Requests] as [Requests]
order by [RequestID]
} as [Requests]

append (
{select [RequestID], [RequestCustID], ..., [TransFS], [TransFSExp] from [Customers]}

relate [RequestID] TO [RequestID]
) as [Customers]

It might be worth mentioning -- in my query results, the repeating section for Customer data is actually missing -- it's NOT that the section is there and the fields are showing up blank... This is true both for the form that I originally created, as well as the basic form that I created as directed a couple of posts up.

Re: Missing query results when two tables are joined

Hilary Stoupa — Thu, 11 Jun 2009 15:19:58 GMT

I know that since they submit correctly the tables are probably linked correctly, but if I wasn't getting results, I think that is what I'd check next. You can click the 'Edit SQL' button in the data connection wizard to see the SQL -- while it is different from the t-SQL you'd use to when you write a query against your database, it may give you more insight.

Re: Missing query results when two tables are joined

kminor — Thu, 11 Jun 2009 13:00:35 GMT

Thanks for the pointer, Hilary -- I had not realized you could do that. I just tried it, and I'm having the same issue. I get the results from my main table (Results), but it doesn't show the results from my other table (Customers). I've got them linked in the main data connection, so I'm not quite sure what's going on. Also, when I submit forms, the data is saved correctly in both tables.

Re: Missing query results when two tables are joined

Hilary Stoupa — Wed, 10 Jun 2009 21:52:26 GMT

One thing that can help you sort out data issues sometimes is to create a new view and drag your entire data source over to it, just select Section with Controls and IP will hodge something together for you. Then you'll have a view that shows everything that you can turn to after you run a query.... it may help you narrow down your issue.

Re: Missing query results when two tables are joined

kminor — Mon, 08 Jun 2009 00:57:07 GMT

Hi, Hilary -- thanks for responding (and so late!).

I'm filling in query fields for my main data connection. I had tried to allow multiple results, but I got errors because my Requests table shouldn't actually be repeating. I'm figuring out that what I want to do here isn't what I should actually be doing.

Ideally, when my users get to the end result of their search, I want there to be only one Request (and its related Customers). Otherwise, if someone had many Requests in the results, and they made changes to one, all would be resubmitted (which in my case means all of them would be e-mailed -- not what I'm going for).

What I'd really like is an intermediate screen between my query and my final, single-record result -- one that could list out each returned Request in a single row, then the user could click on that row (or a button, or a bit of data in that row) and be taken to the data entry form for that record. Is this something InfoPath can do? Or do I need to quit thinking back to my ASP programming days?

ETA -- I'm starting a new thread for this, since my issue has changed...

Re: Missing query results when two tables are joined

Hilary Stoupa — Sat, 06 Jun 2009 05:41:11 GMT

Hi--

What query are you using where you aren't getting the results you expect? Is it the main data connection query that was created when you created the form off the database? Can you provide a little more detail about what you are trying to do?

Or are you filling in the query fields for your main data connection and then running that query? If so, and if you expect to see multiple results returned, you'll need to make sure that in your data connection you have selected the checkbox that allows the return of multiple records. You'll find that in the screen of the Data Connection Wizard that displays your Access tables in the tree view... there is a button that says Modify Table (I think that's what it says -- it may just say Modify). If you click it, toward the bottom there is a checkbox you can select to return multiple records.

Missing query results when two tables are joined

kminor — Fri, 05 Jun 2009 21:02:41 GMT

Hello,

I recently made some changes to an InfoPath form I created. It is connected to an Access database. Previously, it queried/submitted to only one Access table. Now, I've broken the single table out into two tables -- one is a request table; the other is a table called Customers (it represents a repeating section, as there can be more than one customer per request).

When I submit a form, even with multiple customers added to the form, all of the entered data is put into Access correctly. I'm having two BIG problems when I try to run a query in InfoPath against those tables, though:

The results omit the repeating section. It shows the small section of the form that is bound to the request table, and that's it.
Only one request is returned in queries that should have many requests.

I should add that the Requests table has a primary key of Request ID. The Customers table has a primary key of (Request ID and Customer ID). They are joined on Request ID (obviously). I tried making Customers' primary key Request ID (because currently, the data allows me to do so -- just testing to see if the PKs were an issue). I still have the same issue with query results.

I should know better than to try to release a form on Friday afternoon. Now, I'm stuck working on it this weekend. Ugh. Thanks for any help you can offer!

ETA -- I just realized that my form CAN'T display more than one request in the query results because requests aren't a repeating section. So how would I let users search for requests made in a particular month, for example (request month is stored on the Requests table).

Still can't figure out the missing Customers sections. Please share any thoughts you may have on this. I would be VERY appreciative!