473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to combine Records in Access?

2
I receive a .dbf file containing student information on a daily basis. For simplification purposes, it contains the following information:

Name..........Student ID #........Exam 1..Exam 2..Exam 3

Michael Smith....1................95
Jane Johnson.....5........................98
Ana Jordan.......3................92 [exam 1]
Ana Jordan.......3........................97 [exam 2]
Ana Jordan.......3................................90 [exam 3]
John Michael.....11.......................85
Shawn John.......15.......................90 [exam 2]
Shawn John.......15...............................98 [exam 3]

If a student takes more than one exam, his or her record is duplicated and each row contains the score of a different exam. This results in multiple records for each student.

I save this file as an Excel file and import it into Access 2002. The Excel file is linked, so when I receive a new file the following day, I just update the Excel file.
However, I do not want a separate record for each student. I want it to look as follows:

Name..........Student ID #........Exam 1..Exam 2..Exam 3

Michael Smith....1................95
Jane Johnson.....5........................98
Ana Jordan.......3................92......97......90
John Michael.....11.......................85
Shawn John.......15.......................90......98

How do I get Access to do this?
What kind of query would be required?
Jan 26 '11 #1
2 1388
beacon
579 512MB
Does your Excel file actually say [exam 1], [exam 2], and [exam 3] for those students that have a grade for an exam?

If so, I can help you out.

Without the identifier after the grade, I could setup a query that sets up like you want, but right now it would require you to have a blank record entered for each student ID. There may be ways around this, but I'm not familiar with them.
Jan 27 '11 #2
alpine
2
thank you for your reply. i need to check a few things before i can respond.
thanks again.
Jan 27 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Bob C. | last post by:
I am considering combining one large Access db and several small Access db's into a single SQL Server 2000 db. The small db's serve separate but related functions in our operation, and they all...
4
by: mike | last post by:
I have a database table like the following: id|name|item_id|sequence and the following instance data: 1|Apple|419841|1 2|Orange|419841|2 3|Banana|935890|1 4|Lime|959081|1
6
by: MS | last post by:
Access 97 here. I want a simple way to "lock" certain records on a form. Some records remain "live" until all data is available which happens over time. When all the fields are complete, I want...
2
by: Nitin | last post by:
Here I need a help. I have two tables 1) Mis_A 2) Mis_B Mis_A Mis_B Location Revenue Location Sale Mumbai 10 ...
6
by: robertmeyer1 | last post by:
Hi, There are several databases that have the same tables, with different reocrds. I need to be able to combine them all into 1 in an easy manner/best. How can I do this and are there potential...
3
by: jde357 | last post by:
I have MS SQL data that I'm ODBC querring into Access to allow some simple reporting to be done on the data. That data is event driven, so I get records that the particular event is concurrent. ...
2
by: VSS | last post by:
Hello, I need to know what is the maximum number of records Access can hold? I need approx. 6 million records, do you know if Access can hold this amount? Thank you everyone.
1
by: elbatz | last post by:
Hi! Does anyone know the code of how to delete records in access using excel as reference. example: Delete * FROM table1 WHERE table1.ID=excelfile.ID ? Thanks
1
by: dlee360 | last post by:
Hello! So I've been trying to figure out how to do the following in T-SQL: Orig Table: Col1 Col2 Col3 Set A ...
2
by: puT3 | last post by:
How can I make the report do not show any empty records? For example, in this table do not has address...I want the report only shows company that has address
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.