Re-arranging the deck chairs on the Titanic

[creativeimages]

Re-arranging the deck chairs on the Titanic

My current questions are really about how Access was designed to do several actions. Several databases that I deal with have 27, 43, 70 and 96 fields per each record. It appears that Access’ Queries can have a limiting total of 16 fields or actions (Builds). If this is correct, how do I use a single Query to manipulate data in these fields?

Secondly, once a query manipulates the data in 16 fields, how can I get to the rest of the fields in each record?

Third, how do I get the Queried changes fields recorded/written/stored in the table? If the table with original raw data has blank/null/empty fields that to be populated by manipulating the raw data, how do I do that?

Or, if the original raw data is in one table, how can I manipulate the data in that table to record/write/store the information in another table?


Sample Raw Data (In this specific situation):

PERMIT NO. JOB LOCATION OWNER INFORMATION CONTRACTOR INFORMATION APPROVE DATE USE
JOB DESCRIPTION NO. UNITS CONT. PHONE CONST
MAP NUMBER MAG DIST

-02106B0500 13300 POPLAR TREE RD FAIRFAX COUNTY PUBLIC JOHN C GRIMBERG CO INC $400,000 750
A02 000 10640 PAGE # 100 AV 05501 RANDOLPH RD 07/27/04 E
COMM/BLD ADDITIONS TO EXISTING ELEM SCHOOL FAIRFAX VA 22030 ROCKVILLE MD 20852 301-881-5120 2C
045-1- /01/ /0012- SU
-02106B0520 13300 POPLAR TREE RD FAIRFAX COUNTY PUBLIC JOHN C GRIMBERG CO INC $600,000 750
R26 000 10640 PAGE # 100 AV 05501 RANDOLPH RD 07/27/04 E
COMM/INT ALTS TO EXISTN ELEM SCHOOL//HF FAIRFAX VA 22030 ROCKVILLE MD 20852 301-881-5120 2C
045-1- /01/ /0012- SU
-02176B1073 06202 YOSEMITE DR SAYYAD KHALIL M OWNER $2,000 010
O18 000 06202 YOSEMITE DR 07/30/04 R4
SFD/AMMEND TO ADD DECK/NO STAIRS/NO HT// ALEXANDRIA VA 22312 5B
061-4- /06/N /0039- MA


Sample of Manipulated Data:

qryl-Raw-FCBP-Issued Query
ID Expr1 Alpha Street Type Owner Contractor Cost Build Raw2 Code2 Suite Count O-Number O-Alpha
040730-21 13300 POPLAR TREE RD FAIRFAX COUNTY PUBLIC JOHN C GRIMBERG CO INC $400,000 750 A02 000 10640 PAGE # 100 AV 05501 RANDOLPH RD 07/27/04 E A02 10640
040730-25 13300 POPLAR TREE RD FAIRFAX COUNTY PUBLIC JOHN C GRIMBERG CO INC $600,000 750 R26 000 10640 PAGE # 100 AV 05501 RANDOLPH RD 07/27/04 E R26 10640
040827-30 5695 TOWER HILL CI PRESTWICK LLC PORTEN CO. $100 010 O18 000 005515 SECURITY LA 005515 SECURITY LA 08/26/04 R4 O18 5515
040827-34 5697 TOWER HILL CI PRESTWICK LLC PORTEN CO. $100 010 O18 000 005515 SECURITY LA 005515 SECURITY LA 08/26/04 R4 O18 5515
040903-33 5697 TOWER HILL CI PRESTWICK LLC PORTEN CO. $1 010 O18 000 005515 SECURITY LA 005515 SECURITY LA 09/01/04 R4 O18 5515
040910-29 8428 HAYDEN LA JEMIO EDUARDO OWNER $35,000 010 O24 000 08428 HAYDEN LA 09/08/04 R4 O24 8428
040730-29 6202 YOSEMITE DR SAYYAD KHALIL M OWNER $2,000 010 O18 000 06202 YOSEMITE DR 07/30/04 R4 O18 6202
040730-33 7102 REBECCA DR MC MANUS MICHAEL J BRIGGS DEVELOPMENT $73,150 010 O24 000 07102 REBECCA DR 01720N ALBEMARBLE RD 07/29/04 R4 O24 7102
040827-38 5691 TOWER HILL CI PRESTWICK LLC PORTEN CO. $100 010 O18 000 005515 SECURITY LA 005515 SECURITY LA 08/26/04 R4 O18 5515

The number of records per month from my different sources reaches about 50,000 to 60,000. There are many and multiple records that refer to the same job location.

 

[MichaelRed]

Access recordesets are limited to 255 objects. Objects (within a recordset) include fields and Indicies and (at least possibly) other snippets which I'm not quite aware of. It would appear that you have arrived at a poor conclusion based on whatever.



MichaelRed

 

[creativeimages]

In the query, there are 16 columns, once they are "filled," I can not figure how to add more columns. What am I doing wrong? Is there enough information that you might be able to direct me on that I should be doing? Thanks.

 

[Philly44]

From the sounds of it your are new to Access. How are you building these queries?

 

[payback]

You can INSERT Columns by highlighting a column in full (click on the column when the black down arrow is showing - move the cursor just above the Field line) then selecting Insert then Column from the top menu. To date I have not yet found any limitation to columns in a query but there might be.

 

[deduct]

Use the scroll bar along the bottom of the "columns"?

Karl

 

[RickSpr]

You can also put the cursor in a column and choose Insert|Columns from the menu to insert an empty column to the left.

You can also select multiple columns and choose Insert|Columns from the menu to insert that many columns to the left of the selected columns.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[creativeimages]

Thanks everyone. I think that itt preety obvious that I am new to Access; and can use all the help I can get. Again, thanks.