I am trying to generate a Query by week always beginning on a Monday. I pick any date....and want all records in both printed version and chart version i,e, number of safety meetings attended vs number who should have attended for this week. I can enter data with todays date, well any day this week. Then at some future date ask for who attended this week and percentages (another story). Any ideas?
Nav4
I am wanting to filter data in a table that only begins with an alpha character.
Ex. 233333
23444
03226
AAV233
Thus my result should only show the last character above "AAV233"
Please help. Thanks in advance.
I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.
The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.
I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.
I have a query that groups records by date into week numbers using the following expression.
WeekNumber: DatePart("ww",[Date],2)
I would prefer to display the result as the week commencing Monday date.
How?
Is it possible to query using the week number in the year as criteria. For example instead of putting 13/05/05 to 20/05/05 can we use week 12 - the idea is to save on keying info in each time.
View 5 Replies View RelatedHi everyone
I need to run a query that returns the number of bookings made for each week so far this year. However the date is held in DD/MM/YY format. I had a similar query that worked fine for monthly grouping using Month([Start Date])AS FilterMonth but tried replacing month with week, this didn't work.
Any ideas?
Thanks! :)
Hello all, I'm new to this forum.
I'm having problems creating a query for a database (which I am building as part of my university assignment).
The database is a simple ordering system style database, and contains the tables customer, order, order/product and product. I am trying to build a query that can identify customers who have not made an order during the previous week.
I have tried a criterion " <(now()) - 7 " but all this does is show old orders. I am completely stuck. Any help would be appriciated.
In case it is needed here is a list of fields in each of my tables:
Customer:
Customer ID (PK)
Company Name
Company Street Address
Company Town
Company Region
Company Postcode
Order:
Order ID (PK)
Order Taken By
Date (DD/MM/YYYY) ( =now() )
Customer ID (FK)
Delivery Street Address
Delivery Town
Delivery Region
Delivery Postcode
Order/Product:
Order ID (CK)
Product ID (CK)
Quantity Ordered
Product:
Product ID (PK)
Product Description
Product Cost
Any help would be appriciated. Thanks for reading.
I have a database that tracks call-monitoring scores for call center employees.
They are given a bonus based on their scores which is calculated every pay-period.
The following attachment is what I need the query to show. Or perhaps an excel document linked to the database.
Just wondering if you all could take a look at this and tell me what the easiest method would be.
Also, what would be involved.
My major concern is the query being able to differentiate the different fiscal weeks and or payperiods.
I have a query with below formular which return's all records between Sunday and Saturday
Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7
I can get this formular to work when it is looking at one date feild. (Date Received). I would also like it to look at the (date completed) feild so I get all records entered & completed in the previous week.
I have tried putting same formular in the OR critiria as well but cannot get it to show all records at same time.
What am I doing wrong ???
Tks
I got a problem regarding query work week in database.
The database contain data of year 2006 and 2007. When i query about work week, some record is missing.
i wrote the sql statement as
SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);
There is one record missing... which is 31/12/2006 record.
So any idea to eliminate this??
Wondering if anyone can advise...
The 1st/2nd Jan of this year were treated by most people as still being week 53 of 2004, and week commencing 3rd January being week 1 of 2005. In VB I have a formula to do this:
WeekNo = Format(Now(), "ww", vbMonday, vbFirstFourDays)
However my problem now is that I need to replicate this format in a Query within Access. The formula
WeekNo: Format(Date(),"ww")
treats 1st/2nd Jan as week 1, the 3rd as week 2 etc. Any idea how I can get it to treat 3rd Jan as week 1 instead?
There's a challenge for someone!! Help appreciated.
I analyse things by the date they are ordered by the office.
The date gets put into a spreadsheet and i import this into access for the querying.
I need to summarise it by weeknumber and year.
I use the following queries:
To get the week: Week Number: DatePart("ww",[DateOrdered],0,2)
To get the year: Year: Year([DateOrdered])
To put it together: Weekyear: [Year] & "_" & [Week Number]
The problem is when the year changes.
The dates for week 1 for 2014 are between 30/12/2013 and 05/01/2014 and therefore straddles 2013 and 2014.
Therefore when the year and week number is put together the following results are given for that particular week:
DateOrdered - Result of above query
30/12/2013 - 2013_1
31/12/2013 - 2013_1
01/01/2014 - 2014_1
02/01/2014 - 2014_1
03/01/2014 - 2014_1
04/01/2014 - 2014_1
05/01/2014 - 2014_1
All of the above should all read 2014_1 but some obviously are 2013_1. This causes me problems when doing further analysis and cross tabulations.
How can I amend my query so that the above all show the correct year and week number: 2014_1 ?
How can i make a conditional report that can give me some data from the table.
View 1 Replies View RelatedI have a database that lists jobs that need doing, in the jobs table there is a Deadline field, this is a Date Time field that specifies when a job must be finished by.
So I'm trying to make a query, using my limited knowledge, that displays all jobs that need finishing between todays date and the rest of the week.
So I tried this and got nowhere:
Between #(Date())# AND #(Date()+7)#
Hi
Would be great if I could get some help on this:
I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:
It somhow need to be check what the date is today and then caclulate back.
Any help on this would be much appreciated.:D
Thanks
Daniel
I have a table of end of week sales with ProductID, Volume_Sold, Year and WeekNo. I am about to create a historical table of RRP.
What is the best way to set this out so that I can query the two tables to that when I run a query over the two tables I get the correct price depending on the year and week number I am working with.
tblSales
Year int,
Week int,
CountryCode nvarchar (2),
StoreNo, nvarchar (35),
ProductId nvarchar (15),
Volume_Sold int;
My new table
"tblRRP"
Could contain
Year int,
Week int,
CountryCode nvarchar (2),
ProductId nvarchar (15),
RRP float;
The table is only appended to when the price changes. So some products may have a price increase 2 or 3 times a year others once every 18 months. And if the price changes any calculations need to allow for the 2 or 3 different RRPs the Product may have had during the queried period.
So that when I do year on year revenue calculations it works properly.
Hi,
I would like to setup a MS ACCESS database for the 174 styles that we sell. I would like the user to see the item ref, manufacturer, where the goods are packaged, bulk price, packaging costs, total costs of that item (=bulk + packaging costs), M.O.Q, carton quantity, Contact, Email address, Supplier Address.
Could someone suggest a checklist or alternatively a set of procedures i would have to follow to setup this database? I would like the user to be able to type in the item reference so that it brings up all the information stated above, which would be stored in the data table.Is this possible?
I have the the information stated above in an excel spreadsheet format if I link this to an access related database can i setup a search mechanism, if yes please advise how?
I am not so bothered about the way it looks at the moment I would just love to see it up and running.
Please advise the best way for me to approach this? If someone has a "readme" file which would help me set this up, It would most appreciated????
Thanks in advance ;o)
I have a default text value in a field. When I tab to go that field, it is highlighted. But for this specific field I want to go to show the cursor to the left of the text and with it unhighlighted, so I can add text to the beginning.
How can I do this?
I have the following expression which works nicely to group all of my dates into their respective weeks of the year, by week dates beginning Mondays:
WeekStart: ([MyDateField]+1)-DatePart("w",[MyDateField],2)
However, since the week beginning 12/26/05 crosses over both '05 and '06 (as week 53 and week 1), I get two groups of dates recorded for the week of 12/26/05 when I run my query.
Since my chart is based on weekly totals, I can't have two separate totals with the same 'week beginning' date.
How can I adjust my code to get one total group of dates for the week of 12/26/05?
Help greatly-needed!....
Hey all,
I got a massive lump of WORD documents with Avery Mail label tables -- each avery mail label template/table has a mailing address. This how these people store their data. :mad::confused::mad:
So I went through and over a couple days transfered all 458 records to an excel spreadsheet for easy importing to my access mailing database. Problem is that I forgot to clean up a couple of the fields and now I have all these values with unnecessary spaces as the first or last characters for a lot of values.
My question:
Could anyone help me remove the first and/or last character of these values if the character is " "? Would it be easier to do this in excel and just re-import?
Hello,
Im having trouble with Criteria for a query. I have my criteria like this:
Like "*" & [Forms]![NewSearch]![txtSearch2] & "*"
The only problem is, if im trying to search for a last name such as "Aber" the search results come up with not only Aber but also anything that contains Aber, such as the last name Singaber.
How can I make the criteria search from the beginning of the Name, and not anywhere in between?
Please help, going to drive myself nuts :)
Thanks in advance for any help you may be able to provide.
Mateo
When I run a query I want to preceed the result of one of the fields with the letter V.
ie. the field reads GH567Y and I want it to read VGH567Y
Thanks
Lot of questions from me today but anybody know the vb for ongotfocus of a field for the cursor to go the beginning of a field
View 3 Replies View RelatedI have done a half-dozen searches and only gotten 1 option.
There is a simple way to have the cursor at the beginning or end of a field when it arrives in a new field.
I saw the VBA code aboyt setting the length. There is an easier, simpler way to set it so that you don't have to apply the vba code to every single indiviual field.
I remember learning about it here on this forum, but now can't find it.
Can anyone remember how to do this?
Thanks in advance,
Hey guys,
This is my first post here so please forgive if it seems rather elementary.
Here's the deal, I am a web developer finishing up my senior year of college. I have a client that is using a Microsoft Access database on a web site and the site is built with ASP. I have never really used these two items together before and need to write some scripts to do simple database functions.
I have a lot of experience with PHP and MySQL databases and SQL query statements but I haven't figured out the syntax to go with Access and ASP.
Is there a good beginner's site that covers this? I have scoured the web and found nothing useful.
Also, the following line of code gives me the following error:
objConn.execute("UPDATE Sheet1 SET Salutation='Jose' WHERE Salutation='Mike'")
gives the error
"
Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
/tester/tester.asp, line 22"
That is line 22. I am connected okay because I can do select statements. The table name and field name is correct as well but I don't know why I get this error.
Thanks
I have created a table that contains banking info. I have a field for Transaction #; the Transaction # is always 4 digits. Sometimes that number begins with a 0. When I enter the 0 at the beginning, Access will not show it. I've tried setting the Input Mask (tried #### and 0000) but that still didn't work. The field type needs to be number so that my report will sort properly. How do I get the 0 at the beginning to show?
Thanks for any help you can provide!