a query that will skip nulls

[PatrickB]

I am writing a report. I need a query that or any other method for this report. that can output without null values. I have not used access report builder much.


example

i need a list of programs for a user

the data in the table looks like this

program1 program2 program3 program4
row 1 x x x
row 2 x x
row 3 x x x x
row 4 x


i need the query or another method to put this on my report

row1
program1, program3,program4

row2
program2,program4

row3
program1, program2, program3, program4

row4
program3

i know this is probably something pretty simple to do. I am just kidn confused on how to do it.
Also the user information is pulled from the same table as the programs associated to the user.

thanks so much in advance.

-Patrick

 

[KenHigg]

The only way I can see to do this at the moment is to concatenate all the flds.

It kinda looks like your db needs some normalization. What exactly does this db do, if you don't mind me asking?

ken

 

[PatrickB]

The only way I can see to do this at the moment is to concatenate all the flds.

It kinda looks like your db needs some normalization. What exactly does this db do, if you don't mind me asking?

ken

just housing all kinds of data at the moment. but i am using it to pull reports for what programs each users have. i also use it to pull how many users total have program1 etc.

what do you recommend to normalize the data.

its pretty straight up. there is a X in the field if the user has that application.

I have a lot of oracle experience but I wasn't there for the oracle database startup so this is actually one of my first databases that i am creating from the start.

Is there away to use a text box and just conjuct all the fields together it won't that way it won't matter about nulls.

it would look like this or similar in oracle.

textbox1 = tblname.col1 || tblname.col2 || tblname.col3 etc.

maybe i am missing the mark completely.

 

[KenHigg]

Normalize: Looks like the program flds warrant their own table. You would then need three tables. A user table, an application table and an table to tie the the together, may called a uerApp table. The would be a one to many, user -> userApp. And a one to many application -> userApp. (I think I have this correct.)

It would look something like:

tblUser
userID / userInfo1 / userInfo2
100 / johnDoe / dept200
200 / jillSmith / dept300

tblUserApp
recordnumber / userID / AppID
100 / 100 / 2500
200 / 100 / 2510
100 / 200 / 2500
200 / 200 / 2520

tblApplications
recordnumber / appInfo1 / appInfo2
2500 / MSWord / Vers x
2510 / MS Access / Vers x
2520 / MyCustomApp / Ver x


Make sense?

ken

 

[PatrickB]

Normalize: Looks like the program flds warrant their own table. You would then need three tables. A user table, an application table and an table to tie the the together, may called a uerApp table. The would be a one to many, user -> userApp. And a one to many application -> userApp. (I think I have this correct.)

It would look something like:

tblUser
userID / userInfo1 / userInfo2
100 / johnDoe / dept200
200 / jillSmith / dept300

tblUserApp
recordnumber / userID / AppID
100 / 100 / 2500
200 / 100 / 2510
100 / 200 / 2500
200 / 200 / 2520

tblApplications
recordnumber / appInfo1 / appInfo2
2500 / MSWord / Vers x
2510 / MS Access / Vers x
2520 / MyCustomApp / Ver x


Make sense?

ken

any faster way to get this done? there is over 400 applications. Any given user has at most 35.

 

[KenHigg]

Looking back at your original intentions:

program1 program2 program3 program4
row 1 x x x
row 2 x x
row 3 x x x x
row 4 x

Looks like you'd ended up with 400 'program' fields.

I think the proposed solution just looks like a lot.

BTW: How many users do you have and do you not a have a standard build for these workstations? And that brings up one other suggestion, I think I would set this up as a workstation -> application solution as opposed to the user -> application model...

???

ken

 

[PatrickB]

Looking back at your original intentions:

program1 program2 program3 program4
row 1 x x x
row 2 x x
row 3 x x x x
row 4 x

Looks like you'd ended up with 400 'program' fields.

I think the proposed solution just looks like a lot.

BTW: How many users do you have and do you not a have a standard build for these workstations? And that brings up one other suggestion, I think I would set this up as a workstation -> application solution as opposed to the user -> application model...

???

ken

its based on department no department has over 35 or so applications.

tables are setup by department. in the table u have all the info for that department. i understand this isn't the best way. I just don't have time to fix it to be the best way. I can do cross table counts I do believe to get numbers on the masses. total users are about 1200 or so. biggest department is 300 users. there is roughly 45 departments

 

[KenHigg]

1200. I'd say that's substantial!

Well it seems then that concatenating the fields may be your only solution (even though it's a short term one).

 

[PatrickB]

1200. I'd say that's substantial!

Well it seems then that concatenating the fields may be your only solution (even though it's a short term one).

any tips on making it look like a list doing the concatenate?


another thought i had...

do a check through the database dump each answer that isn't null in that field into a temp table. just some ideas. create a row for each non null value. at the end delete tempfields. i dunno how exactly to do this in access report builder.

in a big mess and i gotta get something done in a hurry. i have only been working with this data for 2 days.

 

[PatrickB]

any tips on making it look like a list doing the concatenate?


another thought i had...

do a check through the database dump each answer that isn't null in that field into a temp table. just some ideas. create a row for each non null value. at the end delete tempfields. i dunno how exactly to do this in access report builder.

in a big mess and i gotta get something done in a hurry. i have only been working with this data for 2 days.

ps thanks ken for the help!

 

[KenHigg]

Glad to help... Post your solution(s)...

 

[PatrickB]

Glad to help... Post your solution(s)...

what did you think about the temp table solution?

go through each program field if not null create a record in a temp table

then in the report display the temp table. kind of like a suedo list.

 

[KenHigg]

Hey, at this point, if the end results are correct, go for it. I do think one of us is missing something, concatenating the fields should be easy. Could you post a copy of your db with just this problem table a hundred or so records?