I'm trying to allow my users to do some analysis of existing data. they would like to be able to generate a report which will summarize sales orders against the month they were placed. They would also like to summarize this by between 1 and 4 other fields.
I have created a "filter" form to allow them to choose the values for each of the 4 fields and my plan is that they will leave them blank if they do not want to narrow them down. My problem is that I would like to remove the grouping on the field if they do not select a value for it.
Example data:
Field1, Field2, Field3, Field4, Cost, Month, Year 1,2,3,4,£20,4,2014 1,3,4,5,£10,4,2014 1,2,4,5,£5,4,2014
If they select nothing for any of the fields I would just want to see that my total for April 2014 is £35
If they select 1 in Field1 then I want to see one line with a total of £35 (i.e. it ignores the distinction of the other selectable fields)
If they select 1 in Field1 and 2 in Field2 the total would be £25
If they select 2 in Field2 and 4 in Field3 the total would be £5
etc. etc.
I am struggling because I need the distinct records to allow me to group on them when I need to but if I don't need to group on them I need to ignore the grouping.
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
I don't know why is it so complicated to add an auto number field in a query. I would like to add a increment number (auto number) on each line and then an auto number on each product.
I use an append query to insert this new record into the table EVENTS_MAIN.
I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.
Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.
Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?
I'm trying to create a query that generates random numbers for each record, sorts them by that field, then selects the top record. This should randomize the record being selected.
I can use the Rnd([ID]) function which does appear to generate a random number. Problem is that each time I exit the program and come back in, it always selects the same record. When I remove the Top = 1, to show all the records, every row does have a different random number but it does not appear to be sorting by this field.
If I run the query, here is the number I get: 0.98609316349029
Exit the program, restart, and run the query again: 0.98609316349029
If I refresh the query, the second and third time does appear to be random but the first result is always the same. how to generate truly random numbers?
The field SECL DDI has the users phone number unfortunately over time these have been entered in different formats so there are 5 digits, 6 digits, 7 digits etc...Can I run a query that counts the number of digits in each filed and then tell me how many of each exist
I'm losing my mind on this one. Here is my situation... I receive a daily excel sheet with these fields. I would like to import this workfile into access and would like to manipulate it anyway I want. The problem I'm coming into is that I can't collapse/group the datetime field into just the date when I run a query. I would like to be able to run a query on any date range the client registered, a query on any and all the unique dates the client purchased something, all the unique clients, etc. Here is a sample
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/4/2007 10:21 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/5/2007 18:20 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 10:21 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 18:22 - 8/6/2007 13:28 Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 01:22 - 8/9/2007 05:46
I would like it to show...
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/5/2007 Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/6/2007 Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007
and a different query like ( the purchase date doesn't matter here)...
Sales Name - Sales # - Client Name - Client # - Registered Dt - Purchased Dt Paul, John - 273 - Kelly, Maria - 2252 - 7/26/2007 - 8/4/2007 Paul, John - 273 - Kelly, Maria - 2252 - 8/7/2007 - 8/9/2007
I read in other places to change the format, input mask, convert, char, etc. but nothing. Please tell me exactly what to do. I don't care if the time changes to midnight or 0:00:00. I will owe you my first born child.
I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.
The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)
The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.
This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!
Code: TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund SELECT [Client Accounting].[Marketer] FROM [Client Accounting] GROUP BY [Client Accounting].[Marketer] PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
NAME START TIME STOP TIME J SMITH 12:00:00 AM 6:00:00 AM S SMITH 3:00:00 AM 7:00:00 AM Z SMITH 5:00:00 AM 1:00:00 PM
I want to calculate how many people work per interval of half an hour. From this example, the results should be:
12:00:00 AM 1 12:30:00 AM 1 1:00:00 AM 1 1:30:00 AM 1 2:00:00 AM 1 2:30:00 AM 1 3:00:00 AM 2 3:30:00 AM 2 ................... 5:00:00 AM 3 5:30:00 AM 3 6:00:00 AM 2
I'm looking for a way to do this. Ultimately, I'm trying to automate scheduled hours in a way that I can produce an IDP (Intra-Day Performance Report). I work in a call center, but unfortunately, we do not have any Workforce Software as of yet. If anyone has any suggestions, I would greatly appreciate it.
Hi, I am just learning Access and are having problems dealing with time intervals. My database form is seperated into two tables and in each column of the tables there are different time periods. The time periods are created by having two columns and one column is the start time while the second column is the end time.The problem is that in order to tie the two database together i have to group the database use the time periods as a key. But since the two tables time periods are different, i have to first seperate time intervals to specific months. My plan first is that using queries, i will insert new rows and in those columns i will have both the start month column to end month column both be the same month, but i could never figure out how to duplicate my information and change only the month. Now i am at a loss and i really need help for the project. I am sorry for the poorly worded explanation but i can't think of a easier way to say it. Please advise me on what to do and thank you for reading.
i have a database that is going to be shared across a network server. What i would like(if possible).
USER 1 has opened up the Microsoft Access database and has spent 15 Minutes entering information. USER 2 then goes to open the same database, but gets a message saying something along the lines of "Database already in use, please try again later.
I have one field containing a couple of 6 digit codes and would like to count the number of codes in the field and write the number to a calculated field. Each code is separated by a comma and a space. How can I count the no of comma within the field?
Query that I have built to create a subform on one of my forms. It's my goal to make the subform easily navigable/query-able for the users, and that is where I've hit a roadblock. The subform contains a field - Balance - which I would like users to be able to search based on numeric/mathematic expressions (i.e. >0 and <40). In testing I have created a text box on the main form (BalanceCriteria), and linked it to the subform's balance field through the query in the Criteria field (forms!MainForm!BalanceCriteria).
This works fine with exact numbers - entering 19 will return client's with a balance of 19 - but returns an error - "Expression is typed incorrectly or is too complex to be evaluated" when tested with a numeric equation (>0).
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
As the title says, i have an identifier field which combines two codes eg. AAAAAA/1234.
I am using a query to extract the number part, however when i try to link to another query it says 'type mismatch'.
I assume that this is because the main query is based on a table where [ShipID] is numeric and the extracted data is based upon a underlying table where [PackageID] is a text field.
I want to get remarks as like this i have a query which is showing the total of different fields suppose the total is 77 and I want to give a crietria field by name of net level which shall work like this ..
if the number is from 0 to 33 then then the result should be C-1 if from 33 to 40 then C-2 if from 41 to 50 then C-3 if from 51 to 60 then B-1 if from 61 to 70 then B-2 if from 71 to 80 then B-3 if from 81 to 90 then A-2 if from 91 to 100 the A-1
How to count the number of times that the data appears in a certain field (which is [Ema!l]). I have a list of ema!l addresses and I want to find out whether that ema!l address has appeared once, twice or more. I want to add this as a field in a query. I don't want to delete it or anything because it's perfectly fine for the ema!l address to appear more than once, I just want to be able to identify when the ema!l address has already appeared.
If I was using Excel I would use a Countif function to count how many times the data in the specified cell appears in the whole column, and that would give me the number. I'm not use to the language of Access so I can't figure out how to achieve this.
I tried adding a Total row to the grid in the query and then changing the total to 'count' but this just returned 1 for every row.
I have a Date field, (an Expiry Date) and I want to use this in a Report that with Group the Report into Months. So we know, such and such a month, these are set to expire. Rather than have a separate Month column... how can I make use of exisiting Date in expiry field. Some one must know... Thanks in advance.
