Want to Extract Specific Data From Query into Table

[rstitzel]

I have a table that is imported from our web server that contains the daily on-line orders. The table contains Cust#, Cust Contact, Cust Phone and each product has it's own field named for the product code. There are about 110 products fields. I want to create a module that will examine each record and extract only the fields that contain data. I would want to extract the data into a table so I could run queries and reports. Can anyone give me some direction?

Thanks

 

[CautionMP]

Couple quick questions, it sounds like your linked web table has about 114 or so fields?
When you "extract the data to a table for queries and reports" are planning to build the table with 4 or so 114 or so fields?

 

[TrojanRabbit]

There are a couple ways that I can think of for putting it into one table.

1) Set up a query that gets the information from the remote source. Use recordset objects, one referring to the query and the other to the table, to update the table. You can use the query or recordset object to inspect the fields for data. The query recordset object traverses the query, and the table recordset object adds records to the table (rst.AddNew ... rst.Update), copying values from the query recordset object. By this method, you first delete records in the table, then add a new record for each record in the query.

2) Set up a make-table query to create the table as an exact replica of the query. Traverse the table and check for empty fields.

I'm not sure exactly what you're trying to do. If you want to have a table for each product since each product has its own field, then you can use method 1, setting the table recordset object to the appropriate table for each record.

 

[rstitzel]

Here's some more information. There's actually 224 product fields. There one for each item named INVENTORY and ORDER QTY. I understand from the orders clerk that it is common for a customer to fill out ALL the fields!

I'm going to close this thread. I'm going to post a new thread regarding creating a report that can handle all 224 fields!

Thanks for you response!