I would like to design and build a database that can match one to one meetings between companies with specific time slots.
For example, we have two sets of people: buyers and sellers. A buyer could specify which sellers they would be interested in meeting. The database would then work out which buyers could meet with which sellers, and when.
I have a flexigrid control which shows 15 minute slots throughout the day. I have a table with timeslots in (start time end time). I need to shade in my flexigrid cells depending on whether on not that slot has been filled in. (booking time twice or more IS allowed)
Whats the best way to do this, do i read through each timeslot record and work out which cell it starts in and which cell it fineshes in - then shade the cells in beween?
Or is there a more efficient way?
Paul
I have a mismatch Query that shows time slots available during day that have not been booked (15 minute intervals) the problem I have i want to just show the time slots that are available depending on what treatment has been chosen so if it was a one hour appointment then it checks that 4 slots available in a row and just displays that
Available
11:00
11:15
12:00
12:15
12:30
How I would try to do this
12:45
13:00
13:15
13:30
so would show for a 1 hour appointment
12:00
12:15
12:30
I have some code that creates appointment that i can send to colleagues, when I run the code first time it work all ok but the second time i run it i get a run time error see pic below
But I don't get any error's if i leave outlook open have also try the code on 2 pc's but stiil same problem
1392336756_tmp_run_time_error[1].jpg
Code:
Shell ("Outlook.exe")
Dim outMail As Object
Set outMail = Outlook.CreateItem(olAppointmentItem)
outMail.Recipients.Add (Me.txtsupervisor)
[Code] .....
I have linked a Shared Calendar from MS Outlook in MS Access. The fields that I need which are Appointment/Meeting Date and Time are not there, only creation date/time. Is there anyway for me to get the Appointment/Meeting Date and Time from Outlook into the table?
Windows 7
Office 2010
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
I need setting up a history table for contacts and the companies that they are associated with. I am sure this will be obvious to some of you database veterans but I am fairly new to Access and I can't seem to figure out the best way to accomplish what I am trying to do.
Here is what I need to do:
When a contact's employment status changes, I need to change the contact's current company association but somehow maintain his or her association with the previous company so that s/he can still be associated with past projects.
So, in my contacts table (TBLContacts), I have a foreign key field "CompanyFK" that links to my companies table (TBLCompaniesPK). There is a one to many relationship between TBLCompanies and TBLContacts.
I want the CompanyFK field to be the current company but somehow link the person with past companies too so that the project directories and subforms will continue to show the contact's association with the parent company.
Maybe I don't need a history table but something else?
I have a similar problem with companies that change name, too. How to deal with takeovers, name changes, mergers, etc.
I have researched the 'party' data model but it is a bit too complex for what I'm seeking. For those familiar with it, I don't really need the intermediary relationship from-to tables.
I'm interested in ideas about setting up a data structure that will allow users to search contacts or select contacts in dropdowns regardless if the contact type is a person or an organization.
Obviously the fields needed for both are different and the biggest issue is the name field because the person contacts are
The way I am accomplishing it now is writing the company name, or "first name " & "last name" for a person, to kind of a bridge table when a new record is inserted into the person table or the organization table...kind of inefficient.
Is this a relationship thing or should I just write a function to create a temporary recordset when needed?
I have a table of companies, with fields that contain data for 2008, 2009, 2010, etc.
I'd like to design a query that allows the user to define on a form the field, or the year, they want to query. (by text string or some other way, I am good enough with the VBA that I can figure this part out once the beginning part is figured out)
I want 2009 data, I type in 2009 and get 2009 data from a table with many years' worth of data. But I only need one query for all the years.
I notice that this is easy with reports, just use SQL in the the wherecondition, argument, but I can't find the equivalent for queries. I tried putting the text field from the form in the SQL in the query, but could not get that to work.
My company has a meeting every tuesday where we discuss the status of all our construction jobs. I am trying to set up a database which will allow me to build a report which shows the job number and job status of all our jobs on that particular meeting date. I would like to be able to view the entire job list and pull up the job list by date. The fields I'm trying to store are job # and job status. But I'd like to be able to go back 3 weeks and pull all the job status notes for the entire job list. I can do this by entering a job status field in the job list table but I'll end up having alot of fields in the table which I want to avoid. Is there another way I can enter this info into the database?
Thanks,
Paul
I have a table where for every customer I have they have two lines of data. Both lines of data contain their customer number and then one line has the qty of tickets they have ordered and the second line of data has their name. How am I able to combine this data into one line? I would really appreciate anyone's help. I need this for the morning and I am looking at combining it manually so any help would be appreciated. Thank you.
View 2 Replies View RelatedI would like to have a macro run at a set time each day. I hoped to use .ontime but apparently that doesn't exist in access.
The macro must run at 10:00am each day, the db will be open from 8:30am until 16:30pm so no problem from that point of view +/- a few mins when the staff arrive.
Hi, i cannot load posts from a specified date and time
PostDate = now()
'AID = accountID of the poster
'read post after added into DB
Set rsForum = Server.CreateObject("ADODB.Recordset")
strForumSQL = "Select * From tblPost where tblPost.PostDate = '"&PostDate&"' and tblPost.PostBy = "&AID&""
rsForum.Open strForumSQL, strForumCon,3,3
PostID = rsForum("PostID")
I gets an error when posting messages, i think is because: tblPost.PostDate = '"&PostDate&"'
PostDate is no text field, its a Date field and i tried to use this symbol: ' around postdate, i also tried to not use it, but then i gets another error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'tblPost.PostDate = 12/11/2005 10:32:50 AM and tblPost.PostBy = 6'.
but that is because the date/time format uses spaces.
How can i fix this?
Thanks in advance
I have a form for creating projects in a database. I originally set this up with 5 buttons for when the project is due to be at 1 hr, 2 hrs, etc. Now, they "management" want me change two of these for end of shift of on the current day and start of shift for the next day. This is the code I had before for the 6 hrs:
Private Sub Command152_Click()
Me.DueTime = RoundTime(Now() + 6 / 24, 1800)
End Sub
I'm trying to create a query that will sum the total time of a specific field. It seems to be doing it, however the value is off by by a couple minutes on all my examples.
Table
- BusArrivalTime
- BusDepartTime
Query
In my Query I'm making a new field like this. It correctly figures out the difference.
BusWaitTime: DateDiff("n",[BusArrivalTime],[BusDepartTime])
Report
Next I'm displaying that Query information inside of a Report by putting this in the Text Box on the Report. It correctly displays the time in the Hours/Minutes format.
=[BusWaitTime]60 & Format([BusWaitTime] Mod 60,":00")
Lastly, I'm using another Text Box on the Report to Sum the Grand Total of the Wait Time for all my records. Here is what Im putting in the Control Source
=Sum([BusWaitTime])60 & Format([BusWaitTime] Mod 60,":00")
...and it's summing my records, but the value is off by a few minutes and I cant figure out why.
In my example, I have 3 records with times of 3:14, 1:35, and 3:20. It should be totaling a figure of 8:09 but its coming to 8:14 instead.
Any way I can set a table up with an intrinsic limit, specifically that only ONE record at a time (this will vary) can possibly have value X selected from a lookup field containing assigned values X, Y and Z. Is this possible? If so, how do I do this?
View 2 Replies View RelatedI am trying to create a command button in Access to generate a Meeting Invite. I found code for creating an Appointment and it works great but cannot get it to invite attendees.
Private Sub AddAppt_Click()
On Error GoTo AddAppt_Err
' Save record first to be sure required fields are filled.
[Code].....
stuck with this problem on trying to calculate the aggregate sales totals of a product within a specific time frame. The query that I have built instead divides each sum by date, where it should be grouped according to product instead.
Here are some screen shots as to how it looks in Access. [also see attached ZIP if you don't want to unbreak links ]
[URL]
As you can see, the PRODUCT_ID column is not combining together according to their IDs.
[URL]
This how my Design View looks.
The SQL for my current query is:
SELECT PRODUCTS.PRODUCT_ID, Sum(SALES.SALES) AS SumOfSALES, SALES.TRANSDATE
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY PRODUCTS.PRODUCT_ID, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2008# And (SALES.TRANSDATE)<=#12/31/2008#));
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
View 4 Replies View RelatedI'm trying to have a command button send info on the form straight to Outlook as a meeting. So far, the code below works apart from the address bit. I need the code to automatically add the same 2 email addresses as recepients everytime.
Error message highlights the red line below with the following message:
Runtime error '438'
Object doesn't support this property or method
I'm new to VBA, so don't understand what is wrong here. The reference is set, I've tried different permutations of the "To:" & "Recipients.Add" for the email address with no joy.
Code:
Private Sub cmdSend_Click()
Dim outApp As Object
Set outApp = CreateObject("Outlook.application")
Set outmail = outApp.CreateItem(olappointmentitem)
[Code] .....
I have created a form that list the hours that people do for different types of activities. What I want to do is to sum the hours of each activity. However, I have some activities that I want to group.
For example, I have Training, Ride Outs and Mentor as types of activities that I want to group and then there are all other types that I want to group as Other.
I would just like to add a formula into the command box of properties.
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
Hi,
I have recently been doing a lot of work on this area. Im able to export to where i want to and run macros through the VBA code inside of Access to edit the spreadsheets. This is ok if your making a new excel workbook/worksheet.
But what im stuck on is exporting to a so called template in excel. I can export to it at the moment but creating a new worksheet, in which i have to then cut and paste the data into the correct worksheets through code and then delete the worksheet that i had been working from (which is annoying because you have to confirm the deletion of this worksheet, which is why i couldnt really do the process this way).
What i want to know is there a specific way of telling the data you are exporting from a table/query/querydef to go into a certain worksheet and into a certain cell. For example; a list of names, i want all the Surnames to go into a worksheet called "Claim_Breakdown" and start from cell "A15" downwards until they have all been exported into the worksheet.
Anyone have any ideas on how i could achieve this? Thanks.
I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
View 3 Replies View RelatedI am trying to figure out how to make a button that sends an email to a specific email address, containing the information from 1 row.
we have rows where we put the the address, the quantity and the time interval we can collect the packages in. these information have to be send to a trucking company. I want access to send an email to the trucking company's email address, and not a whole report of all the rows, but be able to choose to send row 1,2,3 etc.
so i have an interesting question and im hoping that someone can help on this one. i need to pull date from a specific table, no problem, that's written and working fine, next i need to be able to join the data from another table by a primary key, again no problem. third, i need to be able to select the date (using WHERE) for a specific date range. (i.e. i enter the date range of 01/7/2007 to 15/7/2007) and the query comes back only showing the data from that specific time, not the data from before or after. this is where my problem lies, all the entered data is being shown after entering my date range. i am going to include my SQL statement, just so you can actually see what im really talking about.
SELECT srealest.Name0, srealest.Dist1, SREpayments.Face2Pd, SREpayments.Penalty2Pd, SREpayments.[2paid], SREpayments.Face3Pd, SREpayments.Penalty3Pd, SREpayments.[3paid], SREpayments.Face4Pd, SREpayments.Penalty4Pd, SREpayments.[4paid], srealest.Map, srealest.Parcel, srealest.LeaseHold, srealest.TaxRebate1, srealest.TaxFace1, srealest.TaxPenalty1, srealest.TaxYear, srealest.BillNo, srealest.PdRebate1, srealest.PdFace1, srealest.PdPenalty1, srealest.DatePd
FROM SREpayments INNER JOIN srealest ON SREpayments.BillNo=srealest.BillNo
WHERE (((SREpayments.[2paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[3paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[4paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date])) Or (((srealest.DatePd) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]))
ORDER BY srealest.Name0;
any thoughts or ideas on how to accomplish this would be greatly appreciated!