Simplify repetitive Code

[amerifax]

There is a term "Permit" in my code referring to the table. Is there a way to not have to use it but one time? Like a variable?

SELECT permit.SEQNO, permit.SEAL, permit.SC, permit.PER_DATE, permit.SB, permit.LOT_SIZE, permit.BLDCERT, permit.BLD, permit.JOBADDRESS, permit.JOB_ZIP, permit.JOB_CITY, permit.JOB_PHN, permit.PHN_SEARCH, permit.SPEC, permit.OWN, permit.OWN_FST, permit.OWN_SEC, permit.OWN_LST, permit.OWN_PHN, permit.OWNADDRESS, permit.OWN_CITY, permit.OWN_ST, permit.OWN_ZIP, permit.OCCUPANCY, permit.CON_TYPE, permit.STORIES, permit.FOUNDATION, permit.USE, permit.HAVC_EQUIP, permit.SEWER, permit.SEPTIC_NO, permit.WATER, permit.ENERGY, permit.PRICE, permit.SQ_BASE, permit.SQ_LIVE, permit.SQ_GRG, permit.LAT, permit.LON, permit.IPDATE
FROM permit
WHERE (((permit.PER_DATE)>=#1/1/1989#) AND ((Left([sc],2)) In ("14","20","28","30","40","45","51","59","64","66","67")));

Thanks for the help

Bob

 

[Isskint]

Because it all comes from one table, only use it at FROM and in the WHERE criterion.

 

[Galaxiom]

Further to Isskint's advice, even if there is more than one table,a field can be addressed without its tablename if the fieldname is unique within the source tables.

Another useful technique is aliasing.

SELECT A.somefield, B.anotherfield
FROM VeryLongTableName AS A
INNER JOIN AnotherLongTableName As B
ON A.somefield = B.yetanotherfield
WHERE B.anotherfield = whatever
ORDER BY A.somefield

 

[amerifax]

Thank you both for your help.

But Galaxiom you will never know how much you would just help me. I'm converting from dBase IV to access. Every database. That little statement about a unique field changes everything.

Thank You Very Much!
Bob

 

[amerifax]

As long as I'm on this project.

Is there a way to exclude. Sine I have many fields but at times I need to exclude only a few from the results.

Bob

 

[Galaxiom]

No exclusion. Unfortunately you have to select the names of the fields you want.

However using the query designer saves a lot of time. You can paste SQL text directly into the designer's SQL view of the query then work on it in the designer. However the designer only shows the outer level of queries that include subqueries.

 

[amerifax]

>>However using the query designer <<
Yes I agree. I do need. But there seems to be a little less than a zillion to select from. Can you point me in the direction.
Bob

 

[Galaxiom]

I am talking about the query designer built into Access.

Create Query. It will open at the Design View. Then you can change to SQL View by right clicking on the top part of the designer. After you paste the SQL you can switch back to Design View and Access will try to represent the SQL.

If it can't handle the translation it will tell you.