473,705 Members | 4,203 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Converting excel to Access


I have to convert an existing spreadsheet for rejects into an access
database. My problem is I use a load of lookup tabels in excel. How do
I approch this in access. Also, just a general question, can you do
pivot tables in access.


Feb 6 '06 #1
3 2395
On 6 Feb 2006 03:59:13 -0800, "Decreenisi " <cy*********@ho tmail.com>

Typically the lookup tables become new tables. E.g. tblRejects has
fields ReasonID and ReportedByID. These are linked to tblReasons
(ReasonID, Reason) and tblEmployees (EmployeeID, FirstName, LastName)
etc. In a form where you enter rejects, you offer multi-column
dropdown lists where the user thinks he selects a Reason, but he's
really selecting the hidden ReasonID which is stored in the
tblRejects.Reas onID field.

Pivot tables are called Crosstab queries in Access. There is a wizard
entry when creating a new query, or you can study the Transform



I have to convert an existing spreadsheet for rejects into an access
database. My problem is I use a load of lookup tabels in excel. How do
I approch this in access. Also, just a general question, can you do
pivot tables in access.


Feb 6 '06 #2
Holding a different opinion:
On forms, lookups in Excel can often be replaced with combo boxes in
Access. The multi-column dropdowns referred to above are used to fill
text boxes (form fields) using a .column property in Access.

Acess as a relational database, pulls Excel Lookups into a single table
view by using queries that are linked between two or more tables. So,
if your Excel table refers to a customer or product key for rejects,
you can pull fields from the related table into a reject report
directly (without using the dropdown form fields mentioned in the first

Pivot Tables are pivot tables in Access. Access 2003 performs Pivot
Table work in Access, prio versions call to Excel to perform Pivot
Table work.

Remember, Access is a relational database programming language. Once
the data is stored in an access database, you can show, update, and
report in many ways.

Feb 6 '06 #3
On 6 Feb 2006 08:07:01 -0800, "DoDahD" <da****@gmail.c om> wrote:

Not sure I fully understand. Are you saying we should have a textbox
*and* a (presumably unbound) dropdown, and then writing some code in
dropdown.afteru pdate to set textbox to dropdown.column (x)? What is
the benefit over having a single control: a bound dropdown with a
hidden ID column and a visible Description column?


Holding a different opinion:
On forms, lookups in Excel can often be replaced with combo boxes in
Access. The multi-column dropdowns referred to above are used to fill
text boxes (form fields) using a .column property in Access.


Feb 7 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: John | last post by:
I'd love to hear some expert opinion on my situation. My department relies on Excel as a database, which causes crashing due to resource depleting and basically is just too slow. For example, a user must open seven Excel workbooks so that each can lookup values in the other to rollover into a separate "summary" workbook. Obvious problems there. What is your opinion on how this SHOULD be configured? They suggest using Access initially,...
by: Richard Sherratt | last post by:
The system is an Access 97 front end to a SQL Server back end. There's a whole heap of VBA code. It has its own secure workgroup MDW. It uses automation to open and modify Word, Excel, and Powerpoint documents. It uses automation to create Outlook mail items. e.g. Set objNewMail = objOutlook.CreateItem(olMailItem) With objNewMail ...
by: M. Uppal | last post by:
How do i convert fraction to time in c#. I need to convert 0.25 to time. Excel does this by using Selection.NumberFormat = "h:mm:ss;@" of the Cell Format function. thanks, M. Uppal
by: Ramakrishnan Nagarajan | last post by:
Hi, I am converting Excel data into a Dataset in C#. There are around 24 columns in the Excel Sheet. First I tried to insert one row with correct values in the Excel sheet. i.e. for text columns I entered text values and for numeric columns I entered numeric values. It works fine and pass through all the validation checks and gets inserted into the database successfully. But when I gave some junk values in the excel sheet and tried to...
by: UKuser | last post by:
Hi Guys, I have a program which converts Excel spreadsheets to Javascript and allows interactivity. However it can't convert it to PHP, which is obviously better for users to view (in case J/S is turned off). How would I go about converting some of this created code (example JS below), or is there an easier way to get PHP to do the calculations itself? I am aware of an excel-server product but this is too expensive and doesnt actually...
by: Testguy | last post by:
Hi, I was wondering if one the smart people that frequent this group could give me a hand with a small program I am attempting to debug. I am not a highly experienced developer, but can create small programs. I was asked to write a small application that would look in a folder, and convert all the CSV files to XLS.
by: ShailShin | last post by:
Hi All, I have to convert Excel data to MSAcess .MDB file using VB.NET. VB.NET Code read the Excel file and write it to .MDB file. For the same I have below code, but I am stuck at the writing it to .MDB file. which I have already created with Table as Terms which contains 10 Fields and my Excel contains 10 Fields, Here I want to Extract 3 columns data from Excel to Table. Also it shows error of "syntax error in INSERT INTO Command. ...
by: Michael B. Trausch | last post by:
I was wondering if anyone has had any experience with this. Someone I know is trying to move away from Microsoft Works, and I am trying to look into a solution that would convert their data in a lossless fashion to a more modern format. The database has more than 65K rows, so converting it to be an Excel spreadsheet, would, AFAIK, not be an option. It would seem that MS Works can export the database as a DBF format database, though I...
by: PW | last post by:
Hi, When I run the following command, some fields are ending up blank when the clearly have values them in Excel. I have tried converting the columns to general and text. The ones that are ending up blank (Null, actually) contain: lu24769884l lu24769884xxl
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.