Real Estate Business
Oct 18, 2006
I am building a database for a Real Estate business.
I have 2 tables tblPurchasers and tblVendors.
I intend to write a query that will match the requirements of the purchasers to the properties for sales (from the vendors).
My issue is this (best illustrated via an example);
I run my Query on the 1st April and four properties are produced as being suitable for purchaser 'A'.
Purchaser 'A' immediately deems properties 1 and 2 unsuitable and views property 3 on the 6th April. Property 3 is subsequently deemed unsuitable as well.
I re-run my qury on the 10th April and five properties are produced as now being suitable for purchaser 'A'.
Purchaser 'A' has already dismissed properties 1,2 and 3 so i don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Purchaser 'A' has already dismissed properties 1,2 and 3 so I don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Something involving a yes/no box on a form would be the best method for me of implementing this (as it would fit in with the scematic of the database to date).
I imagine the properties (with a small photo) appearing and tick boxes headed 'Unsuitable', 'Viewed' etc be used to dismiss properties, which do then not re-appear.
MANY THANKS !
View Replies
ADVERTISEMENT
May 19, 2006
BACKGROUND
The current challenge I have in MS Access in really giving me a headache. I can’t seem to find an answer or indeed think “outside the box” so I am hoping there is someone who can help.
Below I will outline the design of my database, the problem I have and my proposed solution. My solution is “What I want to do” rather than “How I do it”. If you have the time and patience to look at my problem and suggest how I proceed, I will be really grateful.
CURRENT DATABASE DESIGN
I created a database that imports daily telephone data. The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.
I extract data from the phone system for “Lines” (3 digit code) e.g. ‘301’,’302’ which each have a corresponding line “Description” e.g. ‘New Customers’, ‘Accounts Queries’. There are three main daily extracts (1) Inbound Calls (2) Outbound Calls and (3) Time. Each extract has either “Line” or “Description” as the unique identifier but not both.
There are 50 lines with matching descriptions and each is allocated to one of 20 teams. I have a query which links all three extracts, groups the data by team and date. Therefore this query creates 20 records (teams) for each day (date).
CURRENT QUERY AND TABLES
Tbl Line Lookup (50 records)
Line
Description
Team
Tbl Inbound Calls
Line
Date
Offered Calls
Answered Calls
Abandoned Calls
Tbl Outbound Calls
Description
Date
Outgoing Calls
Tbl Time
Description
Date
Answer Time
Abandon Time
Talk Time
Wrap Up Time
Qry Grouped By Team Daily
Line – linked to line in Tbl Incoming Calls
Description – linked to description in both Tbl Outgoing Calls and Tbl Time
Team – Grouped field
Date – Grouped filed
Offered Calls - Sum
Answered Calls - Sum
Abandoned Calls - Sum
Outgoing Calls - Sum
Answer Time - Sum
Abandon Time - Sum
Talk Time - Sum
Wrap Up Time - Sum
PROBLEM
Customer Services “Team” is made up of six lines (301-306) and Business Partners “Team” is made up of three lines (307-309). For each day my query creates a record for each of these teams based on how the line is allocated in Tbl Line Lookup.
The business had decided that line 304 calls are part of the Business Partners with effect from 01/05/06. If I change the team name associated with 304 in Tbl Line Lookup this will, incorrectly, allocate all information (from 01/01/06 to present) to Business Partners.
I need a mechanism to allocate 304 to Customer Services prior to 30/04/06 and to Business Partners after 01/05/06.
PROPOSED SOLUTION
I want to introduce a new field in Tbl Line Lookup called Effective Date.
Tbl Line Lookup (50 records)
Line
Description
Team
Effective Date
Then I want to introduce a new query which links the data in the three data tables and allocates the correct team name by comparing the date in these extracts with the effective date in the Tbl Line Lookup.
I would then run my original query (Qry Grouped By Team Daily) over this query instead of the original tables.
Please can someone tell me if it is possible to look up values in another table to determine the value of a field in the way I have described?
View 5 Replies
View Related
Nov 9, 2006
Hi,
This is what i want to do:
Have a button on a form that when pressed opens a file browse, allows you to pick a text file (May contain different values and in future the structure may change slightly), then import the text file contents into a temp table (2 fields, Setting & Value), then run a query to take different values from the temp table and put them in to different fields in to different tables linked to the same main record where the button is, then save the record and all linked tables data, and then delete the temp table, and display a message box saying something like done.
Each import file will be a single record, but has to many fields to be in one table
I also need to extract only part of Field 1 (Sample Below), the part after the = (150 in below)
0x6A,0x10,0x12 = 150
Thanks
Dan
View 14 Replies
View Related
Jul 20, 2006
does anyone know where i can find a dictionary of what VB words mean in plain english so i can write and adjust them.
View 4 Replies
View Related
Apr 27, 2005
Many will look at this and laugh but its real.
My Access Dbs are opened in hidden state and i dont see my forms.
This must be the cause.
I tried to hide the menubars using ghudsons code,i disabled the shift key. But i dont know what has happened.
I can nolonger view them. I have tried every method out and i think the only option is to repair OS, may be it will work.
I have enabled the database which i disabled the shift key but when i hold down the shift key, it loads but the forms are totally hidden, STRANGE! ijust see the grey access background and when i click on it twice from the task bar.It pops up.
I have tried to make another database, use the autoexec macro to call a form which enables all command bars, but this works only for this database and when i press the shift key down, this database hides the toolbars and i only see the grey access background. This is happening on EVERY DB ive developed on this machine. I thought it was the access that is totally corrupted but i tried to reinstall my office and i still get the same results
This is now a NIGHTMARE to me.
When db is shifted to another machine it works fine as before.
This is REAL but can i get some suggestions from you folks before i repair my OS.
View 1 Replies
View Related
Mar 21, 2007
trying to join two tables but get message
"relationship must be on the same number of fields with the same data types"
data types are same, numbers.
Primary table has just jobnoID that are all unique (primary table)
ie 6907, 6908, 6909 etc
second table "jobs by order" (secondary table)
has
unique orderno(order numbers) in column one say 69071 and 69072, 69073
and jobno in second coloumn say 6907, 6907, 6907 for all the above.
trying to link JobnoID primary table (one to many) to Jobno secondary table (many)
Also need to note. This database is a purchase order database. so am trying to load in all past data from excel. data is in already. so primary table above with jobno is an auto number so will generate the new jobno for us. but had to copy and paste blank fields into table to get records upto the number we are upto now, ie job number 7112. I have only put data in secondary table for
jobno's 6885 through to 7112, and even some of these have blank spaces.
Any ideas?
Also how do i do a screen capture and dump in here so you can see relationships etc, which would be a whole lot easier to explain. thanks heaps
Alastair:rolleyes:
View 4 Replies
View Related
Aug 31, 2006
Detailed search cannot answer my time problem. Returning after long break in programming, I am trying to create a form to display TimeIn and TimeOut fields such that actioning an OnClick command (or separate tick-box), TimeIn field will hold current time for that record. Need to set the same for a TimeOut field. Both cases time always to be < 24 hours.
Am so rusty on MS Access putting default value as =now() produces an error!!
Any basic help to wind up the old mind always appreciated.
View 2 Replies
View Related
Jul 17, 2006
Ok, I have access on my computer but my boss does not. (I use my personal laptop). When I finish my project is there a way to allow him to use it without access? Like can I just make it a windows application? If so can somebody please point me in the right direction? Thanks
View 14 Replies
View Related
Sep 17, 2006
Hi:
I try to get the value in a real time. There are two subforms inside a mainform. First user enters value into mainform. Than comes to subform1. And after that on the basis of a choice in subform1, the 2nd subform is open. In 2nd subform i create a listbox with that query.
SELECT [tbl_Events].[PPVVOD_Outlet] FROM tbl_Events WHERE [tbl_Events].[ticketnum]=[Forms]![tbl_PPVResearch]![ticketnum];
But it shows empty. [tbl_Events].[PPVVOD_Outlet] is a subform1
View 1 Replies
View Related
Mar 19, 2008
Good Afternoon All,
I am in need of you help in calculating this. Basically, I don't want the code to count zero. For example, today is 3/19/2008 - Date() should equal = 1 and not zero..
Here's some more examples:[lastfollowup] being 3/18/08
Date() - [LastFollowup] = 2
Here's my code:
DaysOpen: ((DateDiff("d",[LastFollowup],Date(),2))-(Int(DateDiff("d",[LastFollowup],Date(),2)/7))*2)+IIf(Weekday(Date(),2)>5,5-Weekday(Date(),2),0)+IIf(Weekday([LastFollowup],2)=6,1,0)-IIf(Weekday([LastFollowup],2)>Weekday(Date(),2),2,0)
Thank you,
John
View 1 Replies
View Related
Jan 18, 2006
I am setting up a Database for my company, we want to do it in our local branch first then hopefully we can role the idea out to the entire company. I have started the database already just setting up the tables I need, I think my Design is slightly off though. I am asking for the forums help if that is okay, okay here is the setup.
Areas Needed:
Employee
Projects
Clients
Employee Area:
Date of Hire
fname
lname
salary
title
timesheets
etc...
Project Area:
project #
project name
client
budget info
etc...
Client Area:
client name
addy
contact info
etc....
Project area is going to be the main hub for everthing else, the project number is the key to the entire database. client will be attached to a project via project number, employees timesheets tracked by project number, invoices printed by project number. timesheets will generate the invoices to the client. You guys get the idea, I hope that some of you guys can help me in the spare time you have.
Again, i am looking for Design assistance, i will post a sketch of my design in a couple.
View 2 Replies
View Related
Feb 22, 2006
I am trying to calualate the business from an Order Date. The Business Days are Monday thru Friday excluding Holidays. It is for a form that will Highlight (conditional Format) Order over five business days old that have not been completed. Has anyone done this before?
View 2 Replies
View Related
Jan 20, 2006
History:
MS Access 2003
Database up and running over 5 years
Compact and repair daily
250 end users
Security screen with log on to database
Problem:
User signs on and everything runs smooth, then all of sudden can’t access some reports or forms. I call our IT people, usual fix is resetting the profiles. Now sometimes this works and sometimes it does not work. This problem just started a few weeks ago. I try to isolate the problem. I sign on his computer and have no problem. I have the user that is having the problem to sign on another computer same thing, he has problems. So to me it’s the user profiles right?. The IT people think it’s the database. I have tried about everything I can think of, but still have the problem. Also all the other users are up and running fine.
Now the only thing that I can say is, that we are getting up dates from Microsoft on a daily bases now. Our IT people would run them at night instead of during day. Do you think that could do anything to the database? I have spoken to them and they mention its security updates.
Can you give me any other direction that I can go? We are going to have a big pow wow meeting next week, I’ am trying to have something that I can go on.
View 3 Replies
View Related
Feb 6, 2006
Hi there,
Is it possible to control a lighting system from Access using a combination of VBA code or Active X add ons with some specialist hardware and cabling attached to the PC, etc ?
To be more specific, I've designed a Snooker Hall Management program and want to be able to turn the lights on over the Snooker table when you start a game in an Access form...My application has 8 tables but could be expanded obviously.
I'm happy with the Access side of things (first attempt at a real world solution, etc) but to be totally polished a hardware interface to control the lights would really create a 'wow' factor...
Any help or direction would be greatly appreciated :)
Regards
dazza61
View 4 Replies
View Related
Jan 25, 2007
Hello,
My database is due tommorrow and my switchboard has gone crazy on me, it doesnt work anymore. When you scrolll your mouse it then goes through the links thats the only way to get to the section you want.
So question is this, whats the advantage of using it?
Cant I just set some hyperlinks to the items I want and it would be the same. Wondering cause dont know if I should spend time trying to fix it or just make hyperlinks...and its due tommorrow.
Thanks for any opinions.
View 12 Replies
View Related
Aug 26, 2005
Hello, how do i add 2 field numbers in a query together, i have the fields: "basic hours" & "overtime", and a "total". I would like the "total" field to add basic hours & overtime together.
I believe i need to use the =sum function in the query on the criteria bit, is that right?....but forgot what to do :o please help?
View 10 Replies
View Related
Mar 22, 2006
Hi,
Is there any access formula that can calculate the net business days between two dates.
Regards,
Jatz
View 2 Replies
View Related
Jan 24, 2007
Hi. I'm a freshmen currently in college and I'm taking business info systems right now... and well I'm just being paranoid. Anyway, i really hope this isn't at all too confusing or anything... but
For a hw assignment we where sopposed to like dl this database and just do random commands on it. Anyway, we're sopposed to save it at the end... and email the file to the professor who will grade it.
Anyway, I asked her how she would know if we actually did all the steps and shit... and how she could tell (esp if the directions where like... "1. filter this out." then "3. take out the filter."
she replied that access "saves" all the actions in a database in the document so she will be able to see all the actions that we took.
I did all the assignments, and I'm just being paranoid about my work right now... since it's the new semester and the teacher is SO NICE but very picky when it comes to grading.
My question is just to ask if anyone knows the "secret" command/function/button or whatever you do to basically do what the professor would do to grade my database / ie see that I did everything.
I just want to check to see all my work is good before I submit it to her.
Thank you so much for your time and reading this. :)
View 1 Replies
View Related
Nov 17, 2006
I want to use a business card scanner, and use the data for my Access 2000 order database. Most card scanners, can send data to Outlook, can you go from Outlook to Access? Any advise would be appreciate it . . . THANKS!:o
View 2 Replies
View Related
Oct 27, 2006
I need to calculate the difference between to datetime fields. However, i need to exclude weekends, holidays and non-business hours. I also need to return the result in hours, minutes, seconds.
can this be done. if so, does anyone know how to do this, or is there code that i can implement and save for future uses.
thx,
john
View 1 Replies
View Related
Dec 13, 2006
Hi,
I am trying to update a field on a form with a date that is +7 days from a date that is in another field, but the new date must only be a business day. Can anyone tell me how to do this?
Thanks
View 3 Replies
View Related
Jan 22, 2007
SELECT
[(2) MAX BATCH].ID_CASE,
[(2) MAX BATCH].MaxOfDT_BATCH,
NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE
FROM
[(2) MAX BATCH] INNER JOIN NOLDBA_LOG_DISBURSEMENT_DETAIL ON
([(2) MAX BATCH].MaxOfDT_BATCH = NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_BATCH) AND
([(2) MAX BATCH].ID_CASE = NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CASE);
Can someone tell me how to find the ID_CASE's where
NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE is less
than or equal to 2 business days (M-F) of the MaxofDT_BATCH in this query?
View 1 Replies
View Related
Aug 21, 2007
I have a query that calculates business days. However if my end date is missing the query has error codes because the date its looking for is null. Can show me were to put the nz function.
here's the module for the business days
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' "Calculating the workdays between Dates"
' Note that this function does account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0
Do While DateCnt <= EndDate
If Not IsNull(DLookup("HoliDate", "tblHolidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays
End Function
and here's the query
SELECT [Reques_ID], Work_days([Date_Received],[To_Director]-1) AS Turnaround_To_Director,
Work_days([To_Director],[From_Director]-1) AS Turnaround_From_Director, Work_days([To_VP],[From_VP]-1) AS Turnaround_From_VP, Work_days([Position_Number_Requested],[Position_Number_Recieved]-1) AS Turnaround_For_Position_Number, Work_days([Date_Received],[Approval_to_mgr]-1) AS Turnaround_EPS, Work_days([Date_Received],[JOIS_Posted_Date]-1) AS Turnaround_To_Posting, Work_days([Approval_to_mgr],[JOIS_Package_Return_Date]-1) AS Turnaround_For_Package_Return
INTO tbl_turnaround
FROM Artifact;
View 7 Replies
View Related
Feb 10, 2005
I have a date range of 1/01/2005 to 12/25/2005 for example. I need a formula to return the number of days between these dates excluding weekends. Basically I need to know how many business days are between these dates. Thanks for the help.
View 1 Replies
View Related
Jul 22, 2006
I have seen a few modules that let you count how many business days between two dates. What I am looking for is how to count 10 business days from another field.
Say for example I have a txt box that auto populates =Date(). I want to have another txt box that will count 10 business days ahead and put that date in the box.
This is so I can have the db show a due date for reports sent in.
Anyone seen/done/have code for this?
View 2 Replies
View Related
Jul 28, 2015
I have a mdb file that our company has been using for years and would like to continue. But I need to update our business name and address within the file so when I do reports and invoices it reflects the correct information. I am using Access 2013.
View 14 Replies
View Related
