return data if a field is null

[bosk00]

I am using a query to combine 9 queries for the detail to populate a printed form. I am sure that there must be a better way, but I am at a loss. To explain the whole process. The user fills in a form, with an ssn#, a name, an amount and a code number. The form is linked to a temporary table, as none of the data ever needs to be retained. The maximum amount of entries that can be on the final form is 9. The seperate queries look up the code and pass on the data.

SELECT [TEMPProtest].[SSN1], [TEMPProtest].[Claimant1], [TEMPProtest].[Amount1], [T-codes].[Description] AS Description1
FROM TEMPProtest INNER JOIN [T-codes] ON [TEMPProtest].[TCODE1]=[T-codes].[T-code]
WITH OWNERACCESS OPTION;

The others queries are numbered with the entries being 2 thru 9. The idea of the final query is to pull it all togeather

SELECT a.SSN1, a.Claimant1, a.Amount1, a.Description1, b.SSN2, b.Claimant2, b.Amount2, b.Description2, c.SSN3, c.Claimant3, c.Amount3, c.Description3, d.SSN4, d.Claimant4, d.Amount4, d.Description4, e.SSN5, e.Claimant5, e.Amount5, e.Description5, f.SSN6, f.Claimant6, f.Amount6, f.Description6, g.SSN7, g.Claimant7, g.Amount7, g.Description7, h.SSN8, h.Claimant8, h.Amount8, h.Description8, i.SSN9, i.Claimant9, i.Amount9, i.Description9
FROM [Protest Detail Query1] AS a, [Protest Detail Query2] AS b, [Protest Detail Query3] AS c, [Protest Detail Query4] AS d, [Protest Detail Query5] AS e, [Protest Detail Query6] AS f, [Protest Detail Query7] AS g, [Protest Detail Query8] AS h, [Protest Detail Query9] AS i
WITH OWNERACCESS OPTION;

This works fine as long as there is an entry in all fields. If any of them return no data, then the main query has no data. Is there a way to use isnull? Thanks is advance.

Alan
Senility at its finest

 

[SkipVought]

Hi,

Just a point of reference

'Fundamentals Of Relational Database Design'

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[lespaul]

besides redesigning your database to a normalized model (which Skip is absolutely right to tell you to do), in the mean time, try this:

SELECT a.SSN1, a.Claimant1, a.Amount1, a.Description1, b.SSN2, b.Claimant2, b.Amount2, b.Description2, c.SSN3, c.Claimant3, c.Amount3, c.Description3, d.SSN4, d.Claimant4, d.Amount4, d.Description4, e.SSN5, e.Claimant5, e.Amount5, e.Description5, f.SSN6, f.Claimant6, f.Amount6, f.Description6, g.SSN7, g.Claimant7, g.Amount7, g.Description7, h.SSN8, h.Claimant8, h.Amount8, h.Description8, i.SSN9, i.Claimant9, i.Amount9, i.Description9
FROM [Protest Detail Query1] AS a
LEFT JOIN [Protest Detail Query2] AS b ON a.SSN = B.SSN
LEFT JOIN [Protest Detail Query3] AS c on a.SSN = c.SSN
LEFT JOIN [Protest Detail Query4] AS d on a.SSN = d.SSN
LEFT JOIN [Protest Detail Query5] AS e on a.SSN = e.SSN
LEFT JOIN [Protest Detail Query6] AS f on a.SSN = f.SSN
LEFT JOIN [Protest Detail Query7] AS g on a.SSN = g.SSN
LEFT JOIN [Protest Detail Query8] AS h on a.SSN = h.SSN
LEFT JOIN [Protest Detail Query9] AS i on a.SSN = i.SSN
WITH OWNERACCESS OPTION;

again, this will only work if query A has a record.

(I didn't add all of the Access parens, so you may have to tweak this a bit).

Leslie

 

[bosk00]

In reality the database is fairly normalized. Neat article, I enjoyed reading it. Let me see if I can explain this better. The actual database is made up of client information, state information and t-code information. All are in different tables and do not repeat. This is all to generate a protest letter to a state. None of the information that will be entered is saved. Off of the main menu, when the Protest letter button is pushed, it runs a query which asks for client account number. The protest form opens up with all the client information entered so it can be confirmed that it is the correct client. On this form are 9 rows of 4 columns each. Here is a data example, there can be as few as 1 or as many as 9.

SSN Name Amount Code
123-45-6789 Charlie Brown $525.00 100
987-65-4321 George Jetson $225.00 200

The form takes this information and puts it in a temporary table that will be cleared after the letter is printed. The code number is looked up so that the description is returned. After information is on the form, the user picks what state the letter is for, the appropriate state information is pulled from the database and the letter is printed, with the above information all in the correct spaces, the temporary table is then cleared and the entry form is closed. All this is working without a hitch as long as there are 9 rows of information. With the ablve information as the example, I have 7 rows that contain nulls, so querues 3 - 9 will not return any data. I hope this helps.

Alan
Senility at its finest

 

[lespaul]

Then I'm confused as to why you are doing 9 different queries, can you not get all the information in one query?

What's the SQL for the Protest Detail queries? Could you perhaps use a UNION query there so that's there's one query? That way the "null" queries don't affect your outcome.



Leslie