473,326 Members | 2,013 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,326 software developers and data experts.

How do I set up a linked Excel datasource in Access so that people can add a batch of

I am building a database for a highschool so that staff members can track students with poor grades. I have the whole database linked and working however I ran into a slight problem. I was uploading every weeks grades into a new table and building the new week into each relationship. Now I don't mind do that every week, but once I duplicate the database for other schools to use no one is going to know how to update the grades no matter how many instructions I provide.

Is there a way to make a linked Excel source that would upload the same way every week if someone just added data to it?
I want the columns to look like this:

Student ID Last Name First Name Course 10/19 10/26 11/02

No matter whatI have tried I cannot get one linked Excel Sheet to come out looking like this. HELP
Nov 27 '12 #1
5 1536
Rabbit
12,516 Expert Mod 8TB
1) You should not have a new table for each week. This breaks the rules of normalization. You should have one table and include a field that indicates the week that it belongs to.

2) We would need to see your code that you're using to import the data to be able to diagnose what's going wrong.
Nov 27 '12 #2
Nothings going wrong. I just need some instructions on how to get it to read the way I want it to read. Right now when I add data to the exsisting data in The live source it create multiple lines for that class. I want it to read all on one line...
Nov 27 '12 #3
zmbd
5,501 Expert Mod 4TB
1st: You asked for help.
2nd: You were told a good answer.
3rd: Ask yourself why someone like Rabbit who programs these thing for a living would give you that answer?

The reason you are sinking in the bog is because you are trying to use the database the same way you would a spread sheet. Thus, the way you have designed the datbase makes what you want to do a very complicated task indeed - even with a VBA engine behind the curtain to clean up the garbage.

Please, consider reading: 171.Database Normalization and Table Structures

and then follow the tutorial located at: A Tutorial for Access

Once you have had a chance to really read thru these two articles, please come back and we can talk about the actual database structure and how to make your job so much easier!
Nov 27 '12 #4
NeoPa
32,556 Expert Mod 16PB
'Something going wrong' doesn't necessarily mean that code is crashing. Something is going wrong in your setup because it isn't doing what you would like it to do. Nothing more specific than that should be read into what is really quite a straightforward and basic comment.

I know how daunting it can be to be told everything you have so far is wrong and that you need to start again from scratch. When your understanding is already shaky the last thing you need to find is that even that little you thought you knew is unreliable. It leaves you nowhere.

Nevertheless, like swimming instructors, we're not here to let you drown, but to help you to navigate the pool without the requirement for assistance.

It won't be without effort on your part. If you don't like that idea then you won't like it here. If you're happy with that though, I suggest you go back to post #2 and respond with that reassurance that we're here to provide support.

If you also follow the guidance that Z offered you will certainly benefit from any effort expended. Trying to work with databases without a basic understanding of normalisation is asking for trouble. That, you will never be denied - guaranteed.
Nov 28 '12 #5
zmbd
5,501 Expert Mod 4TB
Yes certainly, I mean no Offense.
As a Chemist and Parent helping our children learn is one of the noblest efforts and professions, any reasonable help that can be provided to our Educators is yours for the asking (and a small amount of effort :) )

One more question... which version of Access are you using?
Nov 28 '12 #6

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

Similar topics

2
by: Don Kuykendall | last post by:
I have a database with blank tables that I distribute to customers. Some customers have files of varying types and would like to import them into my database. I would like to be able to have them...
2
by: Danielle | last post by:
I have a database of address that are downloaded from the internet to excel. I have imported the data to access to create mail-outs easily, however, it is necessary for me to keep the excel...
3
by: Santa-D | last post by:
I've got an excel sheet that I need to import into a table, however, I want the function to automatically manipulate the data prior to it being imported. For example, i have a field called and...
0
by: Grip | last post by:
Hi, I have gone throught the group and Microsoft's online help and have seen many suggestions but I am still seeking clarity: 1. I have an excel spreadsheet. Column A contains text that may...
2
by: jalmar | last post by:
Hello....I'm back again with the same question. I still haven't been able to figure out why my query isn't working. I am linking a table to an Excel spreadsheet, that part is fine, it is even...
6
by: rockart | last post by:
sounds easy. am a little green at this. created an access table from an excel file not linked, just an access table created a form for this, nice and pretty for data entry and to view records...
1
by: seuss | last post by:
Can anyone tell me how to set up an update query for an excel datasource? The closest thing I've found is: <CFQUERY NAME="update_localarray" DATASOURCE="#ODBC#"> UPDATE `mappingfile$` SET f4 =...
12
by: slinky | last post by:
Can an Excel spreadsheet or a section of one be embedded into an Access form and serve as a subform from which other parts of the Access form can get data? Thanks!
1
by: ccookson | last post by:
I have a form that prompts the user to select an excel file to be linked into the database. However, Access automatically assigns the data types from text to numbers. How can I change the data...
16
by: Phil Stanton | last post by:
I have a form with a button which is supposed to open an Excel file (With lots of Macros /VBA) in it. The Excel file gets it's data from the Access program Here is the code Private Sub...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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: 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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.