Re: deleting columns in table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

That's how queries work. Every row is going to include every field.

It's not possible to have a query return, say, one row with 2 fields, one
row with 5 fields and two rows with 6 fields.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"witek84@xxxxxxxxx" <witek84gmailcom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:EB528E9D-5273-474D-B2D7-030F75A2A125@xxxxxxxxxxxxxxxx
I had earlier samthing like this

SELECT [id_probki], [Albumina], [Amylaza], [AspAt], [AlAt],
[Bialko_calkowite], [Bilirubina_calkowita]
INTO temp
FROM Biochemiczne
WHERE ((id_probki Is Not Null) Or (Albumina Is Not Null) Or (Amylaza Is
Not
Null) Or (AspAt Is Not Null) Or (AlAt Is Not Null) Or (Bialko_calkowite Is
Not Null) Or (Bilirubina_calkowita Is Not Null));

but this query show all columns that i wrote in select. meybe will be
easier
if I select only one record (in form user will write id)
WHERE id=12 and(field1 is not null or field2 is not null...

I want to see only cells where are data

"Douglas J. Steele" wrote:

Depends on what you mean by "empty". Usually "empty" equates to Null, but
it's possible that it could be an empty field ("" or " ")

Since you only want rows where data exists, you'd want something like:

INSERT INTO TempTable (Field1, Field2, Field3, ...)
SELECT Field1, Field2, Field3, ...
FROM MyTable
WHERE Field1 IS NOT NULL
OR FIeld2 IS NOT NULL
OR Field3 IS NOT NULL
....


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"witek84@xxxxxxxxx" <witek84gmailcom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:AFA6A45F-431D-414E-9847-DFCB6435C1D3@xxxxxxxxxxxxxxxx
Yes but how choose empty column using query?

"Douglas J. Steele" wrote:

All you need is two queries, no code at all.

To delete the contents of the temp table, you need a Delete query:

DELETE * FROM TempTable

To repopulate the temp table with data from an existing table, you
need
an
INSERT INTO query:

INSERT INTO TempTable (Field1, Field2, Field3, ...)
SELECT Field1, Field2, Field3, ...
FROM MyTable

If there's data in MyTable that you don't want inserted into the temp
table,
put an appropriate WHERE clause.

In general, any time you have to choose between using code and using a
query
to do the same thing, always choose the query.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"witek84@xxxxxxxxx" <witek84gmailcom@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in
message news:89EE5839-43D0-4014-8E24-2D5D2BC960B0@xxxxxxxxxxxxxxxx
I want to delete in macro empty columns.
I've got 2 queries. First deleting all data in temp table. Second
serch
all
records in table that have minimum one cell with data. that query
put
data
into temp table. But not every colums have data. I want that my
macro
check
column and if in column we don't have data then will delete. I've
creating
macros only in excel and Í don't know how write this macro. I know
only
this:

dim val as integer

for x=1 to 10 'I have 10 columns in table
val=0
for y=1 to eof 'how is end of records in file in access??
if cells(x,y)<>empty then 'in excel I'm using cells I
don't
know
in access is that same
val=val+1
end if
next j
if val<>0 then
'and now I want to delete column x
end if
next i

Thanks











.

Relevant Pages