Ihave 16 employess...all of which h 2

[TulsaJeff]

Ihave 16 employess...all of which have a personal access 97 database with 4 fields. (name, date, activity, hours).

I want to compile all the databases into a master by linking them together. Is this best done in a query? If so...what type of query? Ya' Gotta Love It!X-)

 

[RickSpr]

There are 2 basic ways to collect all the data from your employees. Both of them require that you link the table from each employee's copy using File>Get External Data>Link Table on the menu. Obviously, all the databases have to be in shared folders on your network to which you have access rights.

The first method is to use a Union query to combine all the identical tables. Create a new query, switch to SQL Design View, and type in (yes, you have to type it in) the SQL statement that selects the fields you need from the first table. Leave off the final ";". Then, append the word "UNION" and follow with the same SQL statement on the second table. Keep this up until you have all 16 tables.

This method has a couple of disadvantages. First, UNION queries are not updatable, so you won't be able to delete the records if that's your intention. Second, the records are spread out over your network, and all of the databases have to be available at the same time; if your employees shut down their systems at night, you're out of luck.

The other method would be to create a master table with the same fields, and use 16 Append queries to copy the employee records to it. (If you're not afraid of doing some VBA coding, you can avoid the duplication). You could then delete them from the employee's database if desired, using 16 Delete queries (or another hunk of code). If necessary, you could do this at different times for different employees. What you end up with is a single table on your own hard disk that contains all the data you need. That would work faster while you're analyzing the data--but it might not be practical if you can't delete the records from the employees' tables.

If you really must link them into a single recordset, the Union query is the only way I can think of. However, if you want this system to work like a distributed database, where your employees work on their separate database files and you need to upload the data at the end of the day (or week or whatever), maybe you should check into the replication features of Access. The replication Synchronize function does this pretty automatically, and walks you through resolving any conflicts in the data. If you consider replication, don't burn any bridges behind you! Test it out well in a set of parallel databases first, because once you go to replication you can't go back again (in those databases). Rick Sprague

 

[TulsaJeff]

Thanks for the info...I will give it a go and see what happens. I may be asking more questions over the next few days.I would appreciate all the help you can afford to offer. Ya' Gotta Love It!X-)

 

[Dreamboat]

Hey, Jeff. Ya ask more questions, we just put more ads up on your questions. Deal?

LOL

techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[TulsaJeff]

hand extended...ready to dealLOL

I have not worked in access for over a year now and I happen to be one of them people that lose what I don't use. Needless to say I am very rusty. But I feel that it will all come back very quickly and I will be up and running like a pro with all the help from this forum. Ya' Gotta Love It!X-)

 

[Dreamboat]

It's all yours, Rick!

[Dreamboat sometimes just gets in on these threads so she can soak up the info. Frankly, she sucks at Access. Anything more than a little normalization and....kaphlewwey!]
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[TulsaJeff]

Rick...can you go into a little more detail on the union query? I remember using one of those about 3 years ago. I figured out how it worked and built a master index that catalogued thousands of entries from different sources. Right now...right now...I honestly do not have the first clue how to begin... prime the pump a little here... Ya' Gotta Love It!X-)

 

[Dreamboat]

Look what I found. I dunno if it's any good or not.

http://www.zdnet.com/pcmag/pctech/content/solutions/db1710a.htm

http://www.hal-pc.org/journal/08access.html

http://www.really-fine.com/SQL_union.html

And, some of the returns I found were right here at Tek-Tips. Just type "union query" (don't use the quotes) into the search box at the top of the screen.
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[AyJayEL]

Hey, I'm watching this thread too as I have the self same thing to do! Some tips on Security would go down well as my databases are Health and Safety and they want them all combined for each department now, but they don't want everyone who updates it to see everyone elses data. (Does that make sense?) Learn something new every day *:->*

 

[RickSpr]

Try this:

    SELECT Name, Date, Activity, Hours FROM LinkedTbl1
    UNION
    SELECT Name, Date, Activity, Hours FROM LinkedTbl2
    UNION
    ...
    SELECT Name, Date, Activity, Hours FROM LinkedTbl16
    ORDER BY Date, Activity;

The individual queries must all have the same number of fields, with the same (or maybe just compatible?) data types. You can have WHERE clauses (even different ones) on each SELECT, but only one ORDER BY for the whole set. The column names of the result set come from the first SELECT.

AndyLane, I take it you're talking about a central database for all your employees, and you want to limit which rows a given person can see, right? Two ways come to mind: Keep a separate table for each employee and control access with table permissions (messy, and requires union queries, which aren't updatable, to get the overall view); or keep all rows in a single table and use VBA code to customize the recordset shown in the form. I'd suggest each form that might reveal too much have a Form_Open that dynamically changes the RowSource to add the employee name or ID to the WHERE clause. Rick Sprague

 

[AyJayEL]

Rick

The second way sounds like just the ticket. I could use the User ID for the 'filtering'. Now if only I knew someone who could point me in the right direction as far as the VBA code. Sigh...

Andrea Learn something new every day *:->*

 

[TulsaJeff]

You guys are great! I finished up the database this morning and am testing it right now. It seems to be working perfectly, "thanks to a little help from my friends..la la la"

One question... I have a form that has 12 option buttons. Each option is a different activity that an employee would perform throughout any given day. When they select an option it puts a number into the table instead of the actual activity.

Now...I know how to do a long if() statement that would convert all the numbers to the actual activity but is there not a way to set up a lookup of some sort like in excel?

1=activity A
2=activity B

etc. Ya' Gotta Love It!X-)

 

[SimonDavis]

Some of us like VB code.

Wierd, huh?

Always glad to advise if you're having problems - ask away.

 

[RickSpr]

TulsaJeff,

Here are three ways to do a lookup:

1. Use the Choose function. This requires that the activity numbers are consecutive beginning with 1.

    strActivityName = Choose(index, "Activity1", "Activity2", ...)

"index" would be your activity number from the option group. The Choose function returns the corresponding item in the list that follows the index.

2. Use an array of activity names in your form's Declarations section to hold the names. Then index the array. Again, the activity numbers must be consecutive, but can start at any number.

    Dim strActivityNames As Variant

Initialize it in your form's Open event procedure:

    strActivityNames = Array("Activity1", "Activity2", ...)

Where you need the activity name:

    strActivityName = strActivityNames(index - 1)

(Change the "1" in the above line to whatever number the activity numbers start with.)
This might look a little confusing, because strActivityNames doesn't appear to be an array. It's actually a variant containing an array of strings, which is what the Array function here returns. Once you've assigned an array to the variant, you can treat it just like it was Dim'ed as an array in the first place.

3. Use a table to hold the activity numbers and names, and use the DLookup function. This is the closest thing to the Excel method.

    strActivityName = DLookup("ActivityName", "ActivityTbl", "ActivityNumber=" & CStr(index))

There, that should give you some options! Rick Sprague

 

[RickSpr]

Andy,

Your problem takes a good deal of work, and it's a generally useful technique, so I created a FAQ for you in the Microsoft Access: General Discussion forum. It's under the Security heading, and it's titled "How can I limit users to seeing only their own data on a form?".

Tek-Tips does a review of new FAQs, so it won't appear until they've reviewed it, but that should be done some time Wednesday.

If you have any questions using the FAQ, I suggest you start a new thread, and include a reference to this thread (copy and paste the Thread number from above, under the subject line). That way, other Tek-Tips users with a similar problem may be able to find it more easily. Rick Sprague