Microsoft access reports/queries

[Executable]

Hi,

I have a question. I have a table of several records each record contains about 20 columns and of course several rows. I would like to generate a query which checks each cell (not each record) and if the cell has a 0 then I want to generate a report that doest not include the value in the cell ( 0 ) nor the name of the cell. I just want to completely ignore the value and the name when generating the report. It can be in VB or a SQL statement that's ok. Is this possible?

Thanks

[jimatqsi]

Ummm, you are going to read this post later and feel a little silly. You've just told us what you don't want to report. What do you want to report?

If the name is a column name, what if row 1 has a zero and row 2 does not? Then what, one test says include the 'name' and one test says do not include the name. Or do you mean a person's name is one of the 20 rows and you want to exclude the person on that row because he has a zero in one of the columns.

It's an interesting puzzle. Tell us more.

Jim

[NeoPa]

I can't say it's impossible as Access can be quite flexible really. It's certainly not simple and I can't imagine a scenario where it might make sense. You appreciate if the data is shown at cell level then each cell would require the column label displayed on each record? Because a cell is missing does not mean that all cells for that column would necessarily also be missing.

I would suggest you give the whole concept a little more consideration, to understand fully what it would involve. Not as a technical problem, but rather as to how it would work logically.

Welcome to Bytes!

[thelonelyghost]

I agree with NeoPa here. The system you're proposing seems like it's poorly formed unless it's for an uber specific situation. I can think of a situation where you may use this, but I can already think of an alternative that's a higher normal form.

Example: Members of a religious organization are part of different committees at different levels. 0 means that they aren't part of that committee whereas 1, 2, 3, etc. indicates their seniority within said committee. This provides for members being part of multiple committees at different levels of seniority.

Fix: Assign each member a unique ID if you haven't already. Create a table with four fields: RecordID, MemberID, Committee, and Seniority. RecordID is a simple autonumber to easily keep track of each record. MemberID is each member's unique ID, mentioned earlier. Committee is the name of the field you took the number from. Seniority is the value from the field you took the number from, i.e. "3".

This makes it much easier to produce a report to exclude all "0" values since they won't even be entered into the new table, as well as a multitude of other benefits involving a higher normal form.

[Executable]

I appreciate all the responses; let me try to explain the situation a little better.

Example: I have a table with 3 columns the names are C1,C2 and C3 (3 columns per row) column C1 contains a unique ID that Identifies the record. The value in C2 or C3 can change from 0 to 5. So let's say C1 contains the ID (value)=1 and C2 contains a value of 0 and C3 contains the value of 3. The user would like to generate a report for ID=1 so the code would have to check if the value in C2 and/or C3 is zero in this case C2 would be zero then the report should Only display C1 and C3 and completely ignore C2's value and name.

[NeoPa]

Have you considered how this data would be displayed if C2's value and name are absent from a line? Bear in mind the absence of a name implies no information to identify which column it's related to.

[Executable]

Have you considered how this data would be displayed if C2's value and name are absent from a line? Bear in mind the absence of a name implies no information to identify which column it's related to.

Hi NeoPa Thanks for your input. Well, what do you think of this approach: fetching the record and searching for any columns that contain a zero, deleting these columns and creating a table with the new non-zero columns then generate the report and finally delete the table? This might be challenging but I can't think of any other way.

[NeoPa]

This sounds very much like you're thinking in Excel. I'm sorry to say this doesn't really work in Access (or any database type environment really).

The best you'll get is to separate out the data into records including the ID and one of the columns. Each of the columns could be handled this way and the results joined together using a UNION ALL query. It would almost certainly need to include both the data and an indicator as to which column it's from. This points strongly at very poorly organised data though.

Perhaps it would be better to take a few steps backwards and consider the real-world application you have in mind, then consider which approach would best fit this.

[Executable]

This sounds very much like you're thinking in Excel. I'm sorry to say this doesn't really work in Access (or any database type environment really).

The best you'll get is to separate out the data into records including the ID and one of the columns. Each of the columns could be handled this way and the results joined together using a UNION ALL query. It would almost certainly need to include both the data and an indicator as to which column it's from. This points strongly at very poorly organised data though.

Perhaps it would be better to take a few steps backwards and consider the real-world application you have in mind, then consider which approach would best fit this.

Thanks to all that reply. I think I just found a solution it is not the most elegant solution but it will do the job. I will use 2 SQL statements:

1) SELECT INTO temp FROM Main this will create a new table with the required ID.

2) DELETE FROM Temp I will use the Temp table to delete any zeros (since there will only be one record at this point) then I can generate the report from this table.

Thanks All.

[NeoPa]

That's very much along the lines I proposed (Not recommended. I couldn't recommend such an approach.) except that you use a temporary table within the process, and you don't appear to have allowed for multiple columns. You do appreciate that you will need various INSERT INTO statements for each of the other columns you want to cover don't you?

PS. You shouldn't need the DELETE statement at all. If each of the SELECT INTO statements are properly filtered there will be none to delete.

[NeoPa]

I can see this as being quite complicated for someone to follow without either a fair amount of experience or an example to look at. I cannot increase your experience so I'll show you a quick example.

Starting with a table of :
Table = [tblData]

Field
ID
Col1
Col2
Col3

The first SQL would be along the lines of :

SELECT [ID]
      ,'Col1' AS [Col]
      ,[Col1] AS [Data]
       INTO [tblTemp]
FROM   [tblData]
WHERE  [Col1] IS NOT NULL

The following SQL would be duplicated with minor changes for each subsequent column :

INSERT INTO [tblTemp] ([ID]
                      ,[Col]
                      ,[Data])
SELECT      [ID]
           ,'Col2'
           ,[Col2]
FROM        [tblData]
WHERE       [Col2] IS NOT NULL

No need for deleting any entries later.

[NeoPa]

Using the same table layout, the UNION version would be :

SELECT [ID]
      ,'Col1' AS [Col]
      ,[Col1] AS [Data]
FROM   [tblData]
WHERE  [Col1] IS NOT NULL
UNION
SELECT [ID]
      ,'Col2'
      ,[Col2]
FROM   [tblData]
WHERE  [Col2] IS NOT NULL
UNION
SELECT [ID]
      ,'Col3'
      ,[Col3]
FROM   [tblData]
WHERE  [Col3] IS NOT NULL

This doesn't need a temporary table and also has less, and simpler, SQL.