Working on a huge project, need some input

[AudiA4_20T]

Hi everyone,



I've been given a pretty significant role in a project I'm working on for a large company (who shall not be named).

Basically there is a master list of 80,000 employees listed by their name and e-mail, and there are a variety of fields listed with their name.

For example, if the fields said something like "Last name, first name, company email, Favorite Color, Cat or Dog, genre of music..." It would look like.

Smith........Bob.........bsmith(at)blah.com....... Blue.........Dog...........Rock

While I have this sheet, I will have a variety of external inputs. One for favorite color, one for genre, etc that will be sending me updates weekly. My question is, what does Access need to match the Bob Smith from my document and their external document and update ONLY their field?

I stumbled across this thread

https://www.accessforums.net/tutoria...urce-4331.html

and it seems like it's similar but I am going to have a variety of inputs every week not just one, and I need to update only certain fields not the whole sheet.

TIA,
Clint

[pkstormy]

I'm not sure if you've already answered these questions but where I usually start is:

1. SQL Server or MSAccess tables? (preferably SQL Server).
2. How many users are using the application? (should the vb script in the code bank be used?)
3. How many external users are using the application? (should citrix, or vpn be used).
4. Are there any slow external connections? (should forms be designed in an unbound fashion for slow external connections to prevent slow record retrieval). - unbound form design example in code repository.
5. Security needed? If so, should SQL Server security be used or a security type table be setup or MSAccess security or security through coding (examples in the code repository)
6. Key fields to add to important tables such as: DateEntered, EnteredBy (getusermethod in code repository), DateModified, and ModifiedBy fields.
7. Level of tracking important data changes - record level or field level (examples in the code repository).

Then I get into the table structure itself. I'm not much of a fan with synching data processes. To me, they are suspectable to problems if the synching fails. SQL Server does an excellent job with maintaining (or synching data) if need be using techniques I've never had problems with.

[AudiA4_20T]

I'm not sure if you've already answered these questions but where I usually start is:

1. SQL Server or MSAccess tables? (preferably SQL Server).
2. How many users are using the application? (should the vb script in the code bank be used?)
3. How many external users are using the application? (should citrix, or vpn be used).
4. Are there any slow external connections? (should forms be designed in an unbound fashion for slow external connections to prevent slow record retrieval). - unbound form design example in code repository.
5. Security needed? If so, should SQL Server security be used or a security type table be setup or MSAccess security or security through coding (examples in the code repository)
6. Key fields to add to important tables such as: DateEntered, EnteredBy (getusermethod in code repository), DateModified, and ModifiedBy fields.
7. Level of tracking important data changes - record level or field level (examples in the code repository).

Then I get into the table structure itself. I'm not much of a fan with synching data processes. To me, they are suspectable to problems if the synching fails. SQL Server does an excellent job with maintaining (or synching data) if need be using techniques I've never had problems with.

I'm fairly new to this whole thing so I'll try to answer the best I can.

1. I've read up on the SQL server and that seems like what I'd need. I can do that in Access?

2. I understand what VB script is but not sure what the code bank is. I will basically be the only user but I will be pulling spreadsheets with 2 control fields from a variety of places and trying to change the variable fields they have in each on the main database

3. There shouldn't be any external users.

4. I should just be recieving excel files with the two control fields and their variable fields in them, so external connections are not really present.

5. None needed

6. I have all of these fields that are the variables given to me from the excel sheets

7. Not really sure what this is.

I'm going to go over the full writeup today and see what I can learn from that. I should have some better answers then. I apologize if I sound like a complete idiot I'm more of a hardware guy than actually writing programs

[AudiA4_20T]

I guess maybe this question might be better for the "import/export" forum?

[AudiA4_20T]

Ok let me try to post this question easier after looking at the "import/export" forum.

Basically I need access to import a spreadsheet, match 2 (possibly more but 2 is fine for now) columns identically in my Database (those will be the control fields), and update the changes in their (each excel sheet's) respective fields

[NTC]

an 80k record size is easily with core Access ability and there is no need to look to the sqlserver product set.

your external data updates really key around the unique cross referencing value. ...in most corporate situations that would be an employee # - since we can have people with the same name.

assuming this is true - then how you implement it is a bit situational. you can link and then do an append/update query - or import the data directly - but which is better really is going to depend on whether the data sent to you is always formatted exactly the same or not.

b.t.w. this is fairly common. I have alot of clients that get data from outside sources (sometimes their own web store) via excel sheets or csv - and then import them into the data. It sounds like in your case you might receive different formatted updates from different people or concerning differing information bits - - so you may end up with a couple different import/link methods depending on each. Just will need to figure each out on its own when you have all the specifics.

Hope it helps.

[AudiA4_20T]

an 80k record size is easily with core Access ability and there is no need to look to the sqlserver product set.

your external data updates really key around the unique cross referencing value. ...in most corporate situations that would be an employee # - since we can have people with the same name.

assuming this is true - then how you implement it is a bit situational. you can link and then do an append/update query - or import the data directly - but which is better really is going to depend on whether the data sent to you is always formatted exactly the same or not.

b.t.w. this is fairly common. I have alot of clients that get data from outside sources (sometimes their own web store) via excel sheets or csv - and then import them into the data. It sounds like in your case you might receive different formatted updates from different people or concerning differing information bits - - so you may end up with a couple different import/link methods depending on each. Just will need to figure each out on its own when you have all the specifics.

Hope it helps.

NTC, You are really spot on here.

Instead of having just an employee # however, I have two fields that HAVE to match in order for me to update. They are "displayname" and "emailaddress" which would just be "Smith, John" and jsmith@company.com respectively.

What I'm curious about also is if I can simply update one column from an external sheet, into a column in Access as long as the names are the same? This may be exactly what access is for but I'm very worried about making sure because possible failures can go unnoticed with 80,000 people in one sheet.

[NTC]

a 2 field cross reference isn't a problem. One can do an update query that affects one row and one field.

without specifics one can't give exact syntax but in general - set it up and trial it - that's the way to go:

set up a select query with your new data & main table - - with 2 joins for your 2 cross referencing fields. as your new data consists of just 1 record - - and your cross reference is unique - - then it should join to just 1 record of the main table.....so this is how you isolate your 1 record..... let's call that TargetQuery

then start another new query as a Select query and put TargetQuery up as the source. bring down just the Main Table field to be updated.....and change it to an UpDate Query.....and put in the [FieldName] in the update to area using the New Data field name.......

Hopefully this gets you going....

[AudiA4_20T]

I'm gonna give this a shot right now and start learning it... Really appreciate the help

[AudiA4_20T]

Since I'm so illiterate in this stuff, I figured I'd attach a picture of what I'm doing. I'm using this website for reference

http://office.microsoft.com/en-us/ac...010076527.aspx

but I have a few questions

[AudiA4_20T]

Hmmm ok it looks like I need to set up a relationship and make "Display Name" and "Email Address" Primary keys?

Reading this at the moment:

http://office.microsoft.com/en-us/ac...010120534.aspx

[AudiA4_20T]

Ok I'm here, but this error message is really confusing me...



I have created the relationships between "Display Name" and "Email Address"

[AudiA4_20T]

Ok I got it to work!

Is there a way to make an "If, than" command for Access? For example:

If Access runs the update Query between "Attempt 1" and "Main Sheet" and "Displayname" and "Email Address" aren't matched, it can add them onto "Main Sheet"? Or is that another query altogether?