Andy D

Hi, all.

I have a number of saved imports and exports in an Access Database that I would like to run systematically and in a particular order (that may change) and then get a message at the end to say that all imports and exports have been completed.

If this were Excel I could put a macro together and have it linked to a button that would run them fine. But I have no idea if this is possible in Access and if macros even work the same way. Can this be done and if so how?

For code purposes if we say the imports need to be done first and are called: Import_1, Import_2, Import_3, Import_4 (etc, and may be added to) and the exports are called Export_1, Export_2, Export_3 (etc and may be added to).

The imports need to be done first. Can this be done? And how do I link it to a button. I am new to Access and am using 2010 and 2013. A real basic step-by-step would be amazing.

Really hope someone can help.

Andy

I have done this before with the same file and it worked fine. But something happened to that file and I had to re-write the macro.

What I have written is now:

"File Location And Name.xlsm", Password:="Beer"

The file opens fine but it asks me for the password and I have to type it in. I don't understand why it doesn't work. Have I added something that shouldn't be there or not added something that should?

Please help as it's getting a little frustrating now.

Thanks in advance.

Andy

Hi, I receive a binary excel file and the date / time comes in the format

30-JUL-15 01.00.00.000000 CET

for 30/07/2015 at 01:00hrs and I would like to split this out in an access query so that one field has the date in format 30/07/2015 and the second column has the time in format 01:00 and so on but I am not sure how to go about it.

I could put some formulae in the excel doc and them import it but i would rather Access did the work. Now...I am an idiot when it comes to Access so if you can help a step by step guide would really help.

Thanks in advance :)

Andy

I want to write a macro that copies some columns (say A-E) in excel and pastes over itself in values.

The number of rows will vary each day though...so on Monday it will be 10,000, on Tuesday 14,598 or so but I want it to stop at the last line and do no more.

Basically so it only copies and pastes what it needs to. I currently have it as a default row number, so I have thousands of calculations going on that I don't need to and it slows the file down.

Is there a way to do this?

Thanks in advance :)

Andy

Hi, all.

I want to write a macro to open up an excel document.

However the excel document that I want to open is password protected. I know teh password but I just wanted to know if it was possible to incorporate this into teh macro so I don't have to manually anter it each time?

If so...how?

Many thanks.

Andy

I am basically pressing the function button on the function bar, getting transpose up and choosing the row that i want to turn into a column on another tab so I don't have to keep copying and pasting but it doesn't work.

What am I doing wrong?

Cheers

HI, all.

I have a list of people with start and finish times on different lines. All I want to do is strip out any duplicates so that the start time will be on the first line for that person (whether they are on 1 line, two lines, 3 lines etc) and the finish time is their final finish time (so the finish time on that person's final line).

The table is below:

ResourceName StartTime EndTime

Ahmad, Zeshan 20:00:00 21:30:00

Ahmad, Zeshan 21:45:00 23:00:00

Aitken, Ian 18:30:00 19:30:00

Aitken, Ian 19:45:00 21:30:00

Alexander, Lauren 09:30:00 10:15:00

Alexander, Lauren 10:30:00 12:30:00

Ali, Nabil 09:30:00 10:15:00

Ali, Nabil 10:30:00 12:30:00

Anderson, Anne 16:00:00 17:30:00

Anderson, Anne 18:00:00 19:00:00

Annear, Chantelle 09:30:00 10:15:00

Annear, Chantelle 10:30:00 12:30:00

Armstrong, David 07:00:00 08:15:00

Armstrong, David 08:30:00 10:00:00

Arnott, Caroline 09:00:00 11:30:00

Arnott, Caroline 11:45:00 12:00:00

Baillie, Cheryl 12:00:00 12:15:00

Baillie, Cheryl 12:30:00 13:00:00

Baillie, Cheryl 13:30:00 15:00:00

I can do it with If statements if the person is on 2 lines but this breaks down when there are anything other than 2 lines.

For example the start time for the first person should be 20:00 and the finish time 23:00.

The start time for the last person on here should be 12:00 and the finish time 15:00.

The gaps are effectively breaks and I want to strip them out.

Hope that makes sense?

Cheers

I know how to do this in Excel with dependent lists and macros etc but it doesn't do it exactly as I want so I am hoping there is an Access whiz out there that will help me out.

I wish to create a form that will add lines to a database stored in Access.

I want it to contain some information specific to the person filling it in first (such as user name, Team Leader name date and time). I can do this in Excel using VLookUps but not sure how to do the same in Access? It also needs to store each of those pieces of information in a new column / field.

I will also need 4 drop downs that will presumably be using the equivalent of Dependent lists. If I use a football theme it might make it easier to understand.

First Drop Down - Contains the country, say UK, Spain, Germany and France

Second Drop Down - Contains the league (this needs to be dependent on the country chosen, so if UK is chosen the options will be Premier League, Championship, League 1 and League 2) and the Spanish Leagues is Spain is chosen etc.

Third Drop Down - Team. So if UK was chosen, then Premier League the teams would be Man Utd, Man City, Liverpool, Cheslea etc

Fourth Drop down - Player. So if Man Utd is chosen all the players from that team are on option, same for Liverpool, Chelsea etc.

The objective for the above would be to vote for player of the week and it records it something like:

<Advisor name>,<Team Leader>,<Date>,<Time>,<Country>,<League>,<Team>,<Player>

on separate columns / fields.

Once the options have been chosen from the drop downs there needs to be a submit button that exports those pieces of information somewhere.

ADDITIONALLY there will be several people using this and they all need to be linked so that when the first person enters their first entry whoever enters the second lot of data, their entries will be added to the line underneath and the next entries are always added to the next line.

Obviously the categories for the drop downs will need to be flexible and changeable.

Hope that makes sense.

If someone feeling particularly generous could put something together and email me directly at andydarly@yahoo.co.uk that would be great.

Appreciate your help.

Cheers.

Using Access 2010 I want to create a database that maybe 50-80 people with use. Will be about 5 or 6 entries per person, some text, some drop downs.

Needs to save to a master database so that when the first person enters a set of data and submits it into record 1 it automatically knows to enter into the next record for whoever submits the 2nd set of data and always goes to the next record for the next set of entries.

Hope that makes sense?

Cheers.

I have a list (of about 500) employee numbers. I want to create an error message if certain employee numbers (there are about 20 and will change) are input into a cell reading those 500. I know I can do this in Data Validation by choosing list and entering all the numbers apart from the ones I want to produce the error.

But what I want to know is if there is a way to have the error message pop up if one of the 20 I want to produce the error is input. And the list of 20 will change to less or more from time to time so is the method robust enough to still work?

Hi all.

I have two tables containing similar sets of data for 2 sets of randomly chosen groups of people. The results are all continuous data.

What I want to test is if there is any significant statistical difference between certain means based on certain sample sizes.

But I can't remember how. Is it a t-test or a z-test or something else?

If anyone knows can you give me a run through like an idiots guide so I can test?

Thanks

Hi all.

I am creating a pie chart that represents the hours 7am - 11pm so each of the sectors will be the same size This part is fine.

I will have layers of data underneath this that will represent staffing levels.

Is there a way I can apply a conditional format so that if the staffing level is below forecast that sector on the pie chart will go red, if it is over forecast it will be green etc?

So, for example there are 10 staff in between 7am - 8am and forecast says we need 5, that sector on the pie chart will be green. If there are 12 staff in between 8am - 9am and forecast says we need 20, that sector will be red.

I am working on Excel 2010 and 2013 so if anyone knows if this is possible either Excel would be great.

Many thanks.

Hi, all. I have yet another Excel / Open Calc question...

I have two columns of data. The first is a list of days of the week. The second is a list of weather conditions. I have tried to combine the two using the COUNTIF and AND functions so, in a third column, I only get the results of, say, the number of times it is sunny on Tuesday.

It is important that it returns a number, which is why I thought of COUNTIF.

I am using Excel 2003 so Open Calc formulae should work too. I've also tried to use SUMPRODUCT but I just don't seem to be able to get it to work.

One day I will be good at Excel :)

Any ideas?

Thanks in advance.

Hi, all.

I am looking to create a drop down menu who's choice depends on a previous drop down.

So if my first field is the answer to the question "which meal are you preparing?" and the choices of Breakfast, Lunch and Dinner are in a drop down menu the choices in the next question, "What are you having?", are dependant on the answer.

So if Breakfast is chosen the choices in the 2nd drop down might be Bacon and eggs, Toast, Cereal and Nothing.

If Lunch is chosen the choices in the 2nd drop down might be Salad, Sandwiches, Pie and chips etc and the ones for breakfast do not appear.

And similarly for Dinner - it has it's own set of responses.

Is this possible to do somehow?

Many thanks in advance.

Hi, all.

What I would like to do is write a macro in MS Excel or Open Office.

I have created a table where the catagories (going down the C Column) are, say, "Name", "Gender", "Age Range", "Favourite Colour"...and so on. The responses (in the D Column) are a series of drop downs.

The responses are all copied to a new line - from C20 through to F20 for example.

I can create a push button and assign the macro to it.

All this is on page 1 and pretty straightforward.

What I want the macro to do is enter the values (not the formulas) into page 3 on the same workbook from C3 through to F3. When this has been done I want it to clear the values from the drop downs I chose in page 1 (so that it automatically clears where I have copied them to in C20 - F20) so that the drop downs are ready to use again.

When the drop downs are chosen again I will have new values in my C20 - F20 on page 1. What I would like the same macro to do is when the button is pressed again to submit the new data the responses are to go in the next line on page 3, so C4 through to F4 and clear the data from the drop downs on page 1.

Then each time the button is pressed it transports the new data from C20 to F20 on page 1 to the next avaliable line on page 3 and resets the drop downs on page 1 ready for the next use.

Is this possible? I haven't much of a clue when it comes to macros. If you could write one so I can just copy and paste it I would be very grateful.

Thanks in advance.

I am looking to either design or buy a template to determine the reasons calls come into a call centre. If designed a web tool will be best as there is no shared drive to store data from, say, Excel or Access (or similar).

There are certain details I need to capture including:

* Different "levels" of data - each level having between 2 and 20 options if possible

* A count screen on the front page so the agent knows how many entries they have submitted

* The database reset from the agents' points of view so it's ready for the next call

* All submitted data to be held for analysis of various types (eg daily / weekly / monthly, volume by agents / teams etc)

The only one I have tried so far is surveymonkey but I do not think that will be fit for purpose.

Anyone got any ideas of where I can build one of these or buy it?

Thanks in advance.