Import Problem Can't Select Different Fields

Nov 15, 2005

I am having a problem with Access, when I import a text file everything goes fine until I get to the screen after selecting to import into a new table. The one where you can rename the fields and select the data types and choose to skip the field. Here you should be able to select any of the fields and make changes, well when I select another field nothing happens it does not change to that field. See the attached image where field3 is picked but the field options still had field1.

Anyone else come across this or have any suggestions for fixing it? I guess I could reinstall Access but it's an office computer and would require getting our IT staff to do it since I don't have access to the Access install files.

The file will import fine if you go to finish but it loses data because the fields are not set properly for the data being imported, and I could always create a table first but sometimes it's just easier to do it at load time.

Thanks for any help,

View Replies


ADVERTISEMENT

Open Dialogbox To Select Excel File To Import To Mdb

May 4, 2006

i have this small code to import excel data into mdb file:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"datapub", "D:datapubcolumnar.xls", True, "datapubcolumnar!"

But I want to improve by call a dialog box for user to choose excel file.
Could anyone can write this code for me.
Thanks in advance.

View 1 Replies View Related

Queries :: Prepare Csv For Access Table Import Using Select Query

Oct 26, 2014

I have generated two queries called "qry_GetWellName_GetAPI" and "qry_GetWellProduction" that extracts all the data I need from a csv to make two tables.

My issue is that I can not gather all the production data into a query formatted properly for a make table query that contains production history by month / api number.

Ideally the data would be placed in a table called tblProductionHistory with the following unique table index --> [api_fk] [data date]. The api_fk would link to the table that was created by extracting well names using qry_GetWellName_GetAPI.

tblProductionHistory field structure I am trying to achieve:

[api_fk]--[data date]--[oil rate]--[gas rate]--[water rate]--[days produced]--[cumulative oil]--[cumulative gas]

Currently the qry_GetWellProduction shows each well name and the year it had production as a separate record from the month names and the month's production values. I need to consolidate the data so each record can be matched to the unique table index.

Is there a sub query I need to use to manufacture a data date for each month that includes the year and last day of that month for that year? Also would I need a separate sub query to match each month's production to the api number?

View 1 Replies View Related

Restricting Fields On Import?

Aug 29, 2007

Hey guys-
New here, and fairly new to Access as well (although I can stumble my way around). I am building a database in which I have to import the data on a daily basis (there won't be much, if any, data entry- just manipulating/Queries of the imported data). One of the fields I am importing is a 'category' type field. There's no way to restrict the data entry aspect of it before I import it into my DB- so I have to work with what I got.

The users type in the categories themselves- many all match the same name/spellings, some do not. So I assume I'm going to have a Category table with all the acceptable categories already entered into it. What I need is some way for the ones that don't fit the acceptable Category names to be flagged- once flagged I need to be prompted for each record if I want to ADD the category to the Category Table, or, if I want to match it to an existing category in the category table. If I choose to match it to an existing category, it will always keep that new category name so that queries and whatnot are consistent.

An Example-
The acceptable Category name might be 'Fruits'.
Upon importing the file- there might be entries that match that exactly, or- some might be entered as-

The Fruits
Fruits II
The Fruity Ones

If these three were to be in the import file- I want to be prompted to change the field entry to 'Fruits' or create a new category name, and associate that with the new name.

How would I go about doing this?
Thanks!

View 1 Replies View Related

Import Yes/no Fields From Excel

Jun 2, 2005

Howdi all,

I am creating a database from many, many excel sheets for monitoring of waterbores. A few of these are fields indicating whether something is done or not. If the thing has been done an X is marked. Therefore in Access I want these to be yes/no datatype and the import to recognise the X as yes.

Is this possible and if so how?

cheers for help

nail

View 2 Replies View Related

Import Delimited CSV File With Over 255 Fields

Mar 4, 2008

Let me preface this question with... I DID NOT CREATE NOR DO I HAVE ANY CONTROL OVER THE FILE I'M NEEDING TO IMPORT INTO ACCESS.

I've got a situation where I'm needing to normalize a delimited .CSV file on a routine basis. The .CSV file has 369 fields. When normalized correctly, the true data should only be about 60 fields.

I didn't think this would be such a hard thing... just import the first 255 fields into one table, and the remaining fields into another table. Then, using a query... normalize the database as necessary.

I've scoured this topic all over... I've seen solutions for "fixed width" files, but not delimited. The only helpful thread I've found says that this is possible only through very complicated parsing through the file.

That's where I'm stuck... This is definitely over my head. If anyone has any help on this I sure would apprecaite it.

View 4 Replies View Related

Import With Unwanted Blank Fields

Sep 15, 2005

Hi,
Im trying to import a spreadsheet from Excel. I use the wizard and I get the sheet imported. The only problem is that I get additional blank fields in my table in Access. How can I make sure that this does not happen? I want to keep on importing into the same table, so these useless empty fields keep on accumulating.
Any help?
Thanx,
Stacey

View 2 Replies View Related

Delimited Date/Time Fields Being Deleted On Import?

May 21, 2007

For anyone that might be able to help me out, I'd very much appreciate it, as this is now number 2 stupid workaround that I'd like to resolve before I need counselling...;)

I have a Date/Time field that I'm importing into my database via .csv files. The field is setup like the example below in every .csv file:

4/2/2007 8:30:00 AM

Access keeps throwing errors and deleting all the field values in this field whenever I try to import. I know that Access does this when a Date/Time field includes data that is not delimited, but these field values seem to be perfectly formatted to me... what can I do to stop Access from chucking these on import?

Right now I'm just importing the data into a "text" field, and then changing it to "date/time" afterwards, but I'm worried that once the table gets too long I won't be able to re-index all those records anymore and my database will be useless since it depends on that field being "date/time" format...

All those better than I feel free to show me how and where I've been stupid:D

View 6 Replies View Related

Select Fields For Query

Sep 15, 2006

I've made a form with list boxes on, the user's selections from which are taken as parameters for a query, created by VBA code.

I want to add a final list box to the form from which the user can select which of the available fields they want to see in the query output (i.e. has the same effect as checking/unchecking the "Show" checkbox for each field on the standard query design grid).

I have written the following code to try and do this, but it isn't working. I'm very new to VBA and wonder if anyone can help me out by showing me what's wrong?


'Build Field List
If Me!lstFieldList.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstFieldList.ItemsSelected
strFieldList = strFieldList & "[" & Me!lstFieldList.ItemData(varItem) & "], "
Next varItem
strFieldList = Left(strCriteria, Len(strCriteria) - 2)
Else
strFieldList = "'*'"
End If

'Create Query String
strSQL = "SELECT Centres." & strFieldList & " " & FROM Centres " & _
"Where " & strCriteria & _
" And " & strCriteriaCtr & strSortOrder & ";"


Running the above gives an error, and the section under "'Create Query String" is highlighted in debug mode so I guess the error must lie there? Either that, or the earlier part where strFieldList is being create is generating something which strSQL cannot interpret.

Many thanks in advance if you can help me.

Gary

View 12 Replies View Related

Combo Box Select With TWO Fields

Jan 28, 2005

I have a database of customers, and I'd like to be able to pull up a specific record on a form. Currently the only way I know how to do this is by ID number (the primary key). I'd like to be able to search by their last name and first name (of course we have people with the same last name), so for instance I'd like to be able to input "Smith" and then have a list of all the smiths to choose from. Is there a way to do this? Thank you!

View 2 Replies View Related

Tables :: Import Excel Into Table / Overwrite Data Of Fields Imported

Mar 17, 2015

I have a table, which contains many fields, around 90. Out of these 90 Fields, around 40 will be imported from an Excel sheet, which has same headers as the 40 field names in my table, in which they have to get copied.

So when I import data, it copies them as new records, instead what I am looking at is, the records in Excel and in Access table has a Unique Key, so when I import data, matching with this unique key, the fields should be overwritten into those records from Excel, instead of adding new records.

E.g:

Say I have 2 records in my access:

ID(auto generated) Fab_name Issue_ID Location Remarks Data_1 Data_2 Data_3
1 Fab1 193 NY Bug OC DC MC
2 Fab2 641 DRS Error AC KC FC

Now when I am importing data from Excel with the following records:

Fab_name Issue_ID Location Remarks
Fab1 193 NY - GRM Solved
Fab2 641 DRS - SGP Forwarded

So when I import the above data into my Access from Excel, based on Issue ID, it should update the values of different fields that are imported from Excel, leaving other fields (data_1, Data_2, Data_3) as it is.

And I need this to be written as a macro, so that user just has to choose the file, which he has to import.

View 6 Replies View Related

Import Excel Data Into Existing Access Table With Foreign Key Fields

Apr 25, 2015

I used to import excel data into access successfully, many times but now I have to import excel data into an existing Access table with foreign key fields, which makes me problems.

Its just doesn't work...and Im sure the forien key fields are the prob cause, the other fields are going well ...

View 1 Replies View Related

Select Latest Date From 4 Fields....

Jul 21, 2005

I have a simple table in Access 2003:

Case# (numeric)
CaseOpen (Date)
CaseClose (Date)
Item# (numeric)
ItemOpen (Date)
ItemClose (Date)

Some of the date fields may be null; If null then that date may NOT be selected

I am trying to write a query that returns:

Case#
Item#
Only the most recent date in any of the four date fields
The heading that matches the selected date field

ie:
Case# = 251
Item# = 4756
Most Recent Date = 7/8/2005
Matching Heading = "ItemOpen"

I have been searching and reading for two days-
Help, please?

TIA

View 1 Replies View Related

Select DISTINCT - Selecting Two Fields

Aug 9, 2005

Hi

I am trying to use a query to populate a combo box. works fine as I am using one table. I want to select DISTINCT records which works. However i need the ID of that record in the select statement. Anyone know how to select two things in a select distinct query??

Select DISTINCT tbl_Aplaws.Level2 FROM tbl_Aplaws WHERE tbl_Aplaws.Level1 = 'Business' ORDER BY tbl_Aplaws.Level2;

this works. but i need to select the tbl_Aplaws.AplawID as well. throws errors

View 1 Replies View Related

Conditionally Select Query Fields

May 10, 2007

Is there a way to select a query field based on conditions? There is a table at work that I need to use for my reporting in which the field names are the months of the year. Since my report is monthly for that specific month only, I have to change my field in the query every single month. I would like to be able to put in some type of code that will automatically select the proper month field.

Is this possible?

Thanks! :)

View 2 Replies View Related

Query With Parameter To Select Certain Fields

Jan 12, 2008

Hi, I need help in creating a parameterized query.

I need to show only certain fields depending on the value of the parameters.
For example, I have a table with fields: ID, Name, Phone Number, Address, DOB.
The parameter can only be value of A, B or C.
If the user input A as the parameter in the query, only field ID, and Name will be shown
If the user input B as the parameter in the query, field ID, Name and Phone Number will be shown.
etc.

Is there any way to do this, rather than to separate the query into 3 queries for each of the parameters? Thank you.

View 1 Replies View Related

Form To Select Fields For Query

Sep 14, 2006

I have written code to write a query with parameters set from selections made in a multiselect list box on a form.

I now want to add a check box for possible fields to include in the query and put these on the bottom of my form to allow users to select which fields they want to be displayed in the query result.

Can anyone advise how I can do this?

Many thanks,

Gary

View 1 Replies View Related

Modules & VBA :: Select All Fields That Contain Textbox Value

Oct 20, 2014

I'd like user to input what he's looking for in a textbox and then to filter all the records that contain that word.

values could be like rome, paris, belgrade

When user inputs rome, all fields containing rome should appear

tblTest like '%rome%'
should this work

tblTest like '" & Me.txtTest & "'
tblTest like '%" & Me.txtTest & "%' "

these give filters in report respectively :
tblTest like 'rome'
tblTest like '%rome%'

I might have something else wrong, but i don't get the desired results. No records are displayed...

View 4 Replies View Related

Adding Static Fields To A Select Query

Nov 15, 2005

I wish to add a static field to a select query i.e a field that does not exist within a table.

Does anyone know how I can do this?

Thanks

View 4 Replies View Related

Creating Non-Calculated Fields In Select Queries

Nov 14, 2005

Hi All!

I am trying to create an updateable non-calculated field in a select query. Specifically, I want to create a Yes/No data type called "Fail".

This is a multiple-user application so I can't solve the problem by using append and delete queries to modify records in a table.

This has turned into a much stickier problem than I anticipated. I would appreciate any insight or advice you have.

View 2 Replies View Related

Using Checkboxes To Select Multiple Fields In An Iif Query

Dec 1, 2005

Hi all,


Firstly, I'm an ex-fulltime access developer who has found himself doing access work again 6 years later; I can't actually believe how much I've forgotten :s

Anyway, I've done a search and havn't found anything that can help me so wondered if anyone could give some advice.

I have an access form with 3 combo boxes and a checkbox next to each of them. these are accessed using a query with 3 iif statements in it stating; (iif checkbox is null, "*", combo_box_value). the whole query looks like this

SELECT Customers.*
FROM Customers
WHERE (((Customers.Partner)=IIf(Forms!frm_rpt_main!check _partner Is Null,"*",Forms!frm_rpt_main!partner)) AND ((Customers.[Type Business])=IIf(Forms!frm_rpt_main!check_type Is Null,"*",Forms!frm_rpt_main!type)) AND ((Customers.[Year End Month])=IIf(Forms!frm_rpt_main!check_month Is Null,"*",Forms!frm_rpt_main!month)));

However, the query only seems to want to pull data from all 3 combo boxes

My issue is that if a checkbox is not ticked, I want the values to be ignored for all 3 combo's.

I've attached a copy of the mdb file as my description probably doesnt make any sense, the specific query is "qry_select_month_partner_type_wname_frm_rpt"


Thanking you all in advance for your help!

Younger

View 6 Replies View Related

Join Two Fields In Select Statement Results

Apr 12, 2006

I'm sure this is easy to do, but I'm not sure how to word my search criteria when trying to look for a solution.


Let's say that I have 2 fields in MyTable: FirstName, LastName.

I've been trying to use the following query, but obviously it does not work:
Select (FirstName + ' ' + LastName) as MyName from MyTable.

I would like to have the following data returned to me in the following format:
John Doe
Jane Doe
Jim Doe
Jill Doe

Thanks,
CRhodus

View 1 Replies View Related

Select From Drop Down To Populate Three Fields In A Table?

Nov 6, 2012

I would like to be able to select a value from a drop-down and by doing so be able to populate three fields in a table.

View 12 Replies View Related

Select All Query Fields For Main Form?

Sep 13, 2013

I have a table with about 3800 records and about 150 fields. It also has 4 tables with related data that each have only one field. I am going to create a query to base my from from. Should I just take the star * and get all the fields for each table or should i manually specify them. There are only about 5 fields out of the 150 that I dont need and I need to see the rest.

View 13 Replies View Related

Select Fields Of Table To Use When Printing Mailing Labels

Jan 2, 2008

I have a table that contains a lot of different information regarding to mailing.

There are columns for Name, Address, Address2, Address 3, City, State, Zip, Country, Base Name, etc.

Some units will only required parts of the above information. I'm using the Name as an identifier in the DB only and Address is the beginning of content that I want to use.

My issue stems out of the fact that the City, State, and Country are not always required but the Base Name, may be used in lieu of the City.

I was looking at making checkboxes next to each of the columns on the form on the field and then using those checkboxes somehow to create a report for Mailing Labels.

I'm just not sure how to set this up.

View 5 Replies View Related

More Than 255 Fields Management In A Select Query Of Access 2000

Apr 26, 2007

Dear sir/madam

I have two tables linked together with one-one relationship with primary key.
The form has been created using a select query using these two tables. total fields at present is 253(table1=25 fields, Table2=228 fields Total = 253 fields)

Now my problem is I have to add 20 fields more to the above. so the total fields now is 273 fields but it is not accepting and giving the error as "Too many keys defined 3190".

how to solve these problem

Thanks in anticipation
L.Ranganathan

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved