Only show fields with records (1 Viewer)

[JPR]

Hello friends,

My db has two main tables (table1 and table2).
Table1 gets its records periodically from a text file that I import and that I cannot change.

Table2 is used to store new data, like changes in phone number, email, addresses, etc. I use this table like an historical log and need to verify the changes in the long term.

I would like now to create a query that returns from Table2 only fields with data. It should not show blank fields.

Appreciate any help. Thank you

 

[vba_php]

I would like now to create a query that returns from Table2 only fields with data. It should not show blank fields.

you can't do it, because query returns are given by a database system in the form of "records" not "fields" (a crosstab query would be a rare exception). you can't do it with code either. perhaps a better way for your to go about storing you data in table 2 would be to use the following 4 field setup

FIELD 1      FIELD 2              FIELD 3             FIELD 4
id           field_updated      old_value            new_value

 

[theDBguy]

Hi. Can you post the table structure for Table2? Thanks.

 

[JPR]

Thanks. I have attached the sample. Very simple.

 

[theDBguy]

Thanks. I have attached the sample. Very simple.

For that structure, you will have to use a UNION query. It would be better if you could change the table structure to avoid this issue.

 

[JPR]

Well actually the query is based on both tables and the field in common is the FileNO.

 

[vba_php]

i'm out of here you guys. dbGuy has you covered JPR.

 

[theDBguy]

Well actually the query is based on both tables and the field in common is the FileNO.

Hi. Not sure which query you're referring to, but take a look at the table structure Adam suggested earlier. That structure would avoid having empty fields.

 

[plog]

Can you demonstrate what you want to occur with data? Please provide 2 sets of data:

A. Starting data from your database. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect your query to return based on the starting data you provide in A.

Again, 2 sets of data--starting and expected results.

 

[The_Doc_Man]

Well, one thought is that you can do this selectively with queries. If you only wanted to see changed phone numbers, you could query " SELECT PHONE_NO_CHANGE WHERE PHONE_NO_CHANGE <> '' " and that would give you a list of those changes.

If you want all changes of any kind, you need to change the structure as noted earlier by Adam. This is essentially an "AUDIT" table. You can look up that topic in this forum under keyword "AUDITING"