Running Multiple Append Queries on the same table?

[Supermansteel]

My team at work uses Cognos to run multiple queries to pull in data. Then they take that data and import into Access and then usually run an Append Query to run a RND function to pull out a few samples for them to review the accounts. I was making a suggestion to my team that maybe if we only ran one Cognos query and pulled in all of the columns that everyone needs to pull samples for each of there tests that this would be better on the time savings aspect. Then only one of us would run this query and pull it into Access and than we can run our Append (to append into another table specific to there test) and/or Select queries off that table to get our results. In the instance of the Append and/Select queries, I don't know if this would take place that multiple people would be running different queries at the same time, but would there ever be an issue of multiple users running different queries off the same table?

[NeoPa]

Most of the explanation made frankly little sense to me, so i don't have a good idea of what you're trying to do. However, your last questioon seems straightforward enough so I'll have a stab at answering that for you.

...
In the instance of the Append and/Select queries, I don't know if this would take place that multiple people would be running different queries at the same time, but would there ever be an issue of multiple users running different queries off the same table?

This can be an issue, but is generally avoidable when simply reading (extracting) data by the simple expedient of making the QueryType a Snapshot.

Queries generally are created with the default type of Dynaset. This can keep data locked, but is almost never necessary for merely reading data.

[Supermansteel]

Most of the explanation made frankly little sense to me, so i don't have a good idea of what you're trying to do. However, your last questioon seems straightforward enough so I'll have a stab at answering that for you.

This can be an issue, but is generally avoidable when simply reading (extracting) data by the simple expedient of making the QueryType a Snapshot.

Queries generally are created with the default type of Dynaset. This can keep data locked, but is almost never necessary for merely reading data.

I hope this makes a little better sense on what I am trying to do. Everyone on my team has there own copy of the front end. Every month each person runs a Cognos query to pull in there Data for each of there tests (only system they allow us to use to pull in the information). Because the way they do it now is they all run there individual Cognos queries and import them into Access and run and Sample populaton out of that table. I was thinking about simplifying this in the sense of creating one Main Cognos query and importing one table into the Access Database and having a few people run there samples off of the same table as me. It would run an Append query and pull out maybe 10-20 samples from this main table into another table set for that particular test. In addition to running these Append queries to pull out samples there might be a Select query or two that also runs. The two Select queries I was thinking about converting over to this main table instead of running the individual Cognos query specific to this test would take about 30-40 minutes to run. The Data size with one main table is about 200k rows. The individual queries are smaller but I feel would more time consuming to run multiple Cognos queries and importing multiple files that I would just have one file to pull samples from using either a Select or Append query. And as I stated I don't know that this would happen, but I couldn't imagine at some point that it not happening, that is if I was running my one test pulling out 15 samples out of this main table and someone decided on my team to also run a Select or Append queries which utilizes this same Main table would there be an issue with accuracy or would it just give that person an error message saying that this table is in use?

[NeoPa]

If you make sure that all the queries are set up as snapshot type rather than Dynaset, then any potential problems will be minimised (as per my earlier post).

I assume (still not very clear I'm afraid) that the "Main" table is stored in a Back-end Access database somewhere, although this is never stated anywhere.

You may need also, to look at the locking policies chosen for Access (Tools / Options / Advanced tab).

[Supermansteel]

Sorry, I wrote so much last time that I forgot to mention this. I am unsure of checking the Snapshot/Dynaset. Maybe also the reason why I didn't ask this is because I feel like this is a really dumb question, but how do I check that or know the differences?

Under the Locking policy I have it as Edited Record, is that ok?

The Main table that I am referring to is not stored in my be because it is too massive (also because there will be times i will need to go back a few months with this data, so I am keeping it somewhere else to maximize its potential for the 2GB storage) to be running every month so I have it stored somewhere else and linked to my front end and it pulls a sample from the Main table to the back end tables. I just wasn't sure if two people could run a sample from the Main table and put it in the back end table if they did it at the same time. It seems like you are leaning towards there would be issues with doing it this way. I thought it would be more time effecient to run 1 Cognos query (and having every column for everyone to utilize for each of there tests) and only import once a month that everyone could use that table to pull there samples out of instead of having to run muliple Cognos queries and importing multiple times into Access.

Thanks for your time,

Matt

[NeoPa]

Sorry, I wrote so much last time that I forgot to mention this. I am unsure of checking the Snapshot/Dynaset. Maybe also the reason why I didn't ask this is because I feel like this is a really dumb question, but how do I check that or know the differences?

Under the Locking policy I have it as Edited Record, is that ok?
...

Snapshot/Dynaset is fairly straightforward, but only if you know where to look. If designed better (IMHO) it would be more obvious. Right-click on an empty area of the query and select properties. It's one of them.

Record level locking is fine. It's more precise than table level, but takes more resources and possibly runs a little slower. Either should work in your scenario,

[NeoPa]

...
The Main table that I am referring to is not stored in my be because it is too massive (also because there will be times i will need to go back a few months with this data, so I am keeping it somewhere else to maximize its potential for the 2GB storage) to be running every month so I have it stored somewhere else and linked to my front end and it pulls a sample from the Main table to the back end tables. I just wasn't sure if two people could run a sample from the Main table and put it in the back end table if they did it at the same time. It seems like you are leaning towards there would be issues with doing it this way. I thought it would be more time effecient to run 1 Cognos query (and having every column for everyone to utilize for each of there tests) and only import once a month that everyone could use that table to pull there samples out of instead of having to run muliple Cognos queries and importing multiple times into Access.

Thanks for your time,

Matt

I would actually say (I have to be careful as I can never know your whole situation) that in most cases, assuming Snapshot queries and record level locking, that you should be fine doing it this way.

[Supermansteel]

Neopa,

Thank you very much for your insight with helping me through this.

Matt

[NeoPa]

I'm pleased if I was able to help Matt :)

[Supermansteel]

I'm pleased if I was able to help Matt :)

I think I am retarded sometimes. The very first time you suggested Dynaset/Snapshot I went to one of my Queries and clicked in a blank spot and went to Properties because I figured what you were talking about would be in there, but I didn't see it and I went up and down three times in the All Tab. So that is why I asked that question yesterday. After you suggested to look there again I went back to a different query this time and did the same thing and saw that it was there. Don't know why I didn't try more than one query the other day.

Thanks again..

I am hoping this workes out the way I would like it to and saves some time for everyone on my team especially since we are a little short staffed.

[NeoPa]

I hope it does :)

[Supermansteel]

I hope it does :)

Neopa,

I came up with a good idea on Friday. Since I am now using the same table for multiple tests and there would be only one table to import. To avoid issues with running at the same time as well as saving them time, I could run/set up a Batch Analysis thru Scheduled Tasks to run a Macro for all of the various tests that utilize this table, so that way when everyone comes in the next day there samples will be completed for them. Although I was also trying to take this the next step further and create a SendObject in one of my Macros to automatically send that person the Results thru a Report, however I was coming up with an issue that I had to grant permission to allow it to process. This would then cause problems with haulting all of my Macros if I had to physically be here to hit the Yes button to allow it. Is there no way of changing something in Outlook to not give me that pop-up?

[NeoPa]

Not that I've managed to find Matt :(

See Outlook : Select Address of a Contact for a discussion of some of the issues (with some useful links).

I'm pretty sure it's possible, but does involve some very involved setup work by the Exchange Administrator, which depends on everything working as stated (It didn't in my domain) and your Administrator's willingness to apply it anyway.

Good luck.

[Supermansteel]

Not that I've managed to find Matt :(

See Outlook : Select Address of a Contact for a discussion of some of the issues (with some useful links).

I'm pretty sure it's possible, but does involve some very involved setup work by the Exchange Administrator, which depends on everything working as stated (It didn't in my domain) and your Administrator's willingness to apply it anyway.

Good luck.

NeoPa,

I haven't found anything about the outlook thing other than the fact of buying a program that will allow it to process (supposedly). I have tried an alternative way and have created a folder to put my results in and have tried to run a Macro with Output to option to put the results in excel format in this folder, however I can't figure out the problem with it. It keeps giving me an error message that there is not enough disk space for the temporary files. I know the location to where I am saving it has plenty of space. I then opened Internet Explorer and deleted all temporary files thru there and then I went to the Temporary Internet Files Folder and deleted everything in there. I have also ran a Disk Cleanup and I am still having that error pop-up, any ideas what else I can do?

[NeoPa]

I've been plagued by similar, unhelpful error messages like that while programming in Excel.

I'm afraid I can't be much help here. I know that this sort of thing is intentionally made hard to resist the spread of mail borne malware. This isn't accidental or just simple complexity.

I'm a little surprised one of the SMTP options wouldn't suit you though. My network is configured to block that sort of thing from non-server machines but I would be surprised if yours was (I was under the impression that's quite unusual). I would have thought the CDO option would suit. If not then i'm afraid I'm out of suggestions :(