Don't display a field

[Chellestone]

I'm in the process of moving a lot of data being stored in an Excel spreadsheet to Access. I've created the table and a query, but the query doesn't do exactly what I want so "HELP". There are a lot of fields that have nothing present. I don't want those fields to display at all. For example....if I have Field A, B, C, and D and field A and C have no data in them, I only want to see field B and D. I don't want to see a field with blank data. I hope all this makes sense. :banghead:

 

[plog]

No it doesn't. I fear this has to do with an Excel-mindset. My gut says you haven't set up your tables properly. My overall advice is to read up on data normalization (https://en.wikipedia.org/wiki/Database_normalization). That's the process of correctly setting up tables. Then run through an SQL tutorial (https://www.w3schools.com/sql/). that's the language you use to run queries.

With that said, can you demonstrate your issue? Provide me starting sample data from your table (include field names) and then show me what data you hope to have the query return.

 

[Chellestone]

Example of my table layout:
Agent Name:
009 Prom
004 Midex
008 APAC
012 BAS
John Doe
5

15

Jane Doe
1

2

April Doe
2

1

Paul Doe
1

1



I can easily see that field “004 Midex” and “012 BAS” have no data, but my table has A LOT more fields. Today these fields may be empty, but a month from now there may be data. I would like to run a query that will only display fields where data is present, so in this example when I run the query it should return this:
Agent Name:
009 Prom
008 APAC
John Doe
5
15
Jane Doe
1
2
April Doe
2
1
Paul Doe
1
1

 

[Chellestone]

Yikes....it didn't display correctly.

 

[Chellestone]

I attached a Word document that has my example in it.

 

[plog]

I attached a Word document that has my example in it.

No you did not. Either zip it and post it, or just use commas to delimit your data:

TableNameHere
FieldName1Here, FieldName2Here, FieldName3Here, ...
Steve, 13, 2/1/2016
Sally, 41, 1/1/2017

 

[Chellestone]

Sorry. You can tell today is my first day posting here.
Test
Agent Name, 009 Prom, 004 Midex, 008 APAC, 012BAS
Steve, 1, , 2,
Sally, 2, , 4,
Sue, 1, , 1,
Stan, 2, , 3,

I can easily see that field “004 Midex” and “012 BAS” have no data, but my table has A LOT more fields. Today these fields may be empty, but a month from now there may be data. I would like to run a query that will only display fields where data is present, so in this example when I run the query it should return this:
Test
Agent Name, 009 Prom, 008 APAC
Steve, 1, 2
Sally, 2, 4
Sue, 1, 1
Stan, 2, 3

 

[plog]

Yeah, you need to normalize. You are storing values in field names--you don't do that in a database. You're stingy on specifics, so I am going to call this a component database--where 009 Prom, 004 Midex, etc are components. Correct my vocabulary and provide more details if you like.

Using that terminology, your simply need a 3 field table like so:

Components
AgentName, text field, name of agent
ComponentType, text field, will hold what is now the field names
ComponentTotal, number, will hold the numeric value you are storing under each component

That means your data now looks like this:

AgentName, ComponentType, ComponentTotal
Steve, 009 Prom, 1
Steve, 008 APAC, 2
Sally, 009 Prom, 2
Sally, 008 APAC, 4
...


When you have your data structured properly, you can then run a query to get your desired results. Again, read up on normalization and work through an SQL query.

 

[CJ_London]

I agree with Plog. Get your data normalised then you can use a crosstab query and it will only display columns with data in it