QBF (Query By Form) Advice/tutorial/example Appreciated!
Jul 7, 2006
Hi,
I have some basic VBA and ADO recordset exposure, and would like to create a form, that:
- allows a user to search 4-5 fields (client & business name based information)
- return options of records to view that match that data
- allow user to make a selection, which then provides another form with full details of that client's record(about 20 fields)
Can anyone provide an example or perhaps guidance on a good tutorial, that will take me through this process gently (and help me understand the process)!
I have a Dbase set up and ready to roll, but I haven't actually created the Dbase yet with Access. I would very much like your opinion on my table layout and relationships. You can view the design here (http://www.joyceandstevieb.com/Dbase.htm) I am hoping to track shipments in and out, plus current stocks of items using Queries and Reports etc. I have read a lot on Normalisation and realise that there a lot of fields in the "movement" table, although a number of them are "yes/no" fields to help with the queries. Any advice is most welcome. and as always, your time is much appreciated.
I am new to Access and not a sophisticated user of computers. I've been given the task of redesigning some forms on an existing data base. I understand how to put the new fields I'm adding into the table associated with the forms I'm working on, but I've having a hard time actually moving stuff around the screen and getting it lined up.
Is there an online tutorial or reference I can go to that will teach me the layout basics?
i am a basic basic user in access, however i have the chore of creating a database for our products and sales reps. i have created a table and sepated the tables out using the analyze table function. it has now given me several tables. thats all i have done.
what i want to do is this: create a form that my sales reps can use to create a quote request and print it out for the customer. i want each quote to be saved as well, per customer, per date.
the sales rep may only have 1 of the 2 items needed to preform the quote. when he types in 1 of the 2 items, i want the other 10 fields pertaining to that item to come up (the descriptions{many fields for that} and price).
i then want to be able to either print, email, or both this quote to the customer out of the dbase.
what do i need to do this? if there is someone out there that can help me with this, i am willing to pay to help set up this dbase for us.
I'm currently using Allen Browns pop up calander in an access database. http://allenbrowne.com/ser-51.html
The calender allows the user to choose and add a date into a field.
When I run the access project i can see the pop up calander on my form, and use and interact with it to add in a date. However when someone else on the network acceses the project, and they open the form with the calander on it they get an error and are not able to use the calander. Is there something that i need to change to allow multiple users access to use the calander?
Hello everyone. Hope your holidays are going well. I have been given the task of developing a DB that will be used to generate reports for scheduling services for clients. These are medically related services and are provided around the clock. I have divided the Services into two broad categories A&B for ease of discussion and I think it helps in the DB design. The reports will display client information, services, service detail, appointment frequency and times. The reports will be printed in duplicate, with one copy for technician providing service and the other for book keeping purposes. I have it in 5 tables: 1. tblClient Auto#-pk Client ID Client Last name Client First name Client Location
(pretty straight foward, single client, main table)
2. tblServices ServiceA1-5&B1-5
(Table with a complete list of all the services offered. Clients can have up to abut 6 different services at once of several different types. The services aren't really related to one another - for DB purposes...I think :D )
3. tblServicesA Auto#-pk ServiceA1-5 Frequency
(The services provided here are really more of a maintenence type of service which occurs about 4times/day around the clock (known as the frequency). The technician observes the client, if service is being utilized,then the time listed on the report will be crossed out, if the service is not used the time will be circled on the report. Services in the A category are very straight foward. Each service has a frequency at which they are checked (as stated above 4 times/day for Service A), technician checks client, if service used, service is charged. Again, pretty straight foward, but wait till we get to ServiceB. The patient can have up to 3 services from this category on their report)
4. tblFrequency Frequency Times
(This table is for the different frequencies at which all the services are checked +/or performed. Each service in ServiceA category has only one frequency (4 times/day) we'll call fID. In ServiceB category, each service is associated with one (actually two, but we'll get to that later) frequency. In this category, there are about 13 different frequencies to choose from. All frequencies are associated with one set of times. For example: fID is associated with 0600, 1200, 1800 & 0000. Again, these times will be displayed on the reports used by the technicians (the frequency will also be displayed on the report). The frequencies of ServiceB are subject to change. When this happens, the technician makes appropriate changes on current report, then will update the frequency for future reports. Wasn't sure if a PK was necessary here.)
5. tblServiceB Auto# - PK ServiceB1-5 Frequency Medication Dosage
(ServicesB3-5 are similar to ServicesA in that they are only associated with a single frequency that will not change. So on the report the service and frequency will be displayed.
ServiceB1 is not too difficult, but instead of having a timed freq/day, it only occurs on a 72hr basis. What needs to be displayed on the report will be the next due date of the service. I was hoping that the technican could just refer to order summary to select a date that the service will need to be 'observed', and enter this data as text in a field on a form. When the service is observed, it then needs to be observed again another 72 hours later, so the date will need to be changed. The problem is, that there are times when the observation of this service occurs before the 72 hrs is up, and times when it is done later than 72hrs, so I can't have the date automatically updated.
ServiceB2, a little tricky. ServiceB, like all the other services will have a single frequency which when selected will display the times the service needs to be performed on a report for technicians to refer to. It also has a secondary frequency that will also need to be displayed on the report, but only as a free text. There are not specific times that are associated with this secondary frequency, and like the date in ServiceB1, I was hoping it could be entered as text in a field on a form. ServiceB2 is also associated with a medication (in some cases up to 3 medications). Each medication has a single dosage.
I'll end it here, hope the explanation doesn't make it seem any more complex than it really is.
Although I have been doing alot of research in MSA recently, I'm still very much a noob. Thanks for any help.
How do i create in the validation rule of the table, a way of checking that an email address has an @ sign in the middle of it. and the general format etc?
I have two database tables: -table one contains c.100 decimal lat/longs and a temperature. -table two contains lat/longs for the whole Earth (lots!) and a temperature for each coordinate.
I want to query table one against table two and return a report which:
-returns a figure which is an average of the temperature four coordinates .5 of a degree N,S,E+W for each test site. -compares the test site temp and the new averaged temp against each other
I'm told this should be quite straightforward but I can't figure out how to do this. I'd love some guidance.
Thanks!
P.S. I'm not worried about points near the poles being closer than those elsewhere at the moment (unless there is an easy way of dealing with this?)
I'm running Access 2000 through Citrix, 20 users internationally from 1 db. I'm working through Citrix network issues to split db with separate FE's for each user. I have sporadic corruption due to this. But this morning I met a new breed of corruption. I preface this with the fact that I run a bat file nightly to do clean up and compact and repair the db. It verifies the db is not in use prior to running. This morning we were presented with a hosed db. The log file said updates ran fine. In my main table the first record was garbage, including the Auto-num field. I found that my Primary key has lost it's index and references had become unchecked. Correcting these issues got it running but does anyone have any clue what might have triggered this or even where I should start looking? Please help as I am at a loss. Thanks.
i have created a form in order to search for records. Attached is a screenshot of how the form looks, when it is first opened. All the combos/textbox are unbound, populated via their respective tables. When opened all combos are set to enable = false, then when you click on a radio button, the respective combo will be enabled.
Here is my question:
At the moment i have 4 queries and in each query i have the reference of a particular combo box i.e. for the singer only, i have a query qrySearchSingers, and in the criteria i have [Forms]![frmSearch]![cboSingerOnly]
I have 3 other similar queries for the other search types. Eventually, when i click on a particular radio button option, i will set the form's record source to the query needed.
This method works perfect, however, i'm not sure if it is the correct approach since i am using 4 similar yet different queries to achieve the desired result.
Before I post on here I always try to look back through the posts for similar problems – however I don’t know where to start looking to solve my problem.
I use access to store and record the details of my sales. As well as the prices, I also store the date of the sale.
I want to be able to add up the value of the sales for each day so I can see how much we sold – but I don’t know how to go about it. I’ve tried performing a query and then applying a filter by form – however when I click on the “Date” column, the drop down selection displays a list of identical dates:
And when I click on one of the dates it only shows the data for that individual record, rather than the data for all the records on that date.
Could anyone please nudge me in the right direction? Should I be looking to perform a normal query? Or do I need to work on a different type/advanced query?
Eventually my intention is to create a form which links the queries to a report so I can print out the data, however for now I’d be overjoyed to simply be able to display the values in a table.
Any help would be greatly appreciated! Thank you for your time.
Hi :DFirst off, i am a complete novice in this area, so be kind:D I’m currently doing a Bsc in Computing, and we have a database module ( my worst subject :( ) the current assignment is to create a DB for a specific task, and one of the objectives is to create a login page for the DB . I tired to use Access's built in 'user and permissions wizard' but had no luck there.I have a staff table, and linked that to a permissions table, I assigned each member of staff a user level (5 levels in all). I then created queries to filter each of the 5 levels, and used parameters in each of the 5 queries to prompt for a username and password. I then created a form for each of the queries. i then tested each of the forms, and it asked for the username and password, and I was in, I thought my idea had worked, however, if I enter I wrong username or password, the form will still appear :( .I’m wondering if there is a way to validate the query parameters? or is there an easier way I can do this? I know i am probably going about this in completely the wrong way. All I need is a main login page, which will offer each user a different GUI.Any advice will be greatly appreciated. As aforementioned, this is not my best area, and this is the first time I have actually used access :(.Thank you in advance :)*EditI have searched th eforums, and cannot seem to fidn anythign on parameter validation, but did find a lot of ways to code a login page. i do not want to do this so i will stick with using access's built in users and permissions. I know that to login you need to click the shortcut that is created, but if i take my DB elsewhere how can i get that login page to be displayed? becuase if you click directly it will nto let you in , u need to click the shortcut, so how could i get around that?? thank you :D
I have created a formated field called DateToCallBack, which is the calculated field. For example when I enter say today's date 03/11/07 in the Date field and enter 10 as the number of days to call back in, the calculation field works just fine and gives me the correct call back date which in this example would be 13/11/07. The problem I am having is, I am trying to set a criteria on the calculated field for a Between [Start Date] And [End Date], but when I run the query it gives me all the day dates in the range I specified properly, but it gives me that in every month, when I only wanted one specific month. For example, I select between 1 Nov 07 and 8 Nov 07 It gives me all the 1st to the 8th of every month, when really I only wanted Nov.
I have a table called QuizResults which has 10 Yes/No fields. The table also has a field called Site.
Each site will take a quiz and the problem I am having is with the statistics side of things.
What I need is a query that will work out the percentage correct for each site. All the data is stored in this 1 table. There are no relational fields as they are not required. I have tried a few different ways but each method I use involves me making many many queries to work this out.
To summarise I need 10 percentage correct fields, broken down by site.
Part of the function of my Db is to produce quotations. I specifically need one table containing line items and will use another table (update query) which will contain the 'quoted for' items. Transfer will be based on numbers of users required. I have the basic idea in mind; i will have a form with drop down to select numbers of users and then some Vb to take records from one table to the other. I can do that fine. The bit I am struggling to structure in my mind is as follows:
In a quote there will be typically three line items
1) Software 2) Installation / Configuration 3) User Training
I can easily do as described above but that relies on the main table being pre-populated with all three line items. however, I'd prefer to have the ability to have prices for daily rates, relating to installation and training, in another table so as to be able to change/update them and not have them fixed in a table.
So I think I would want to have the three line items update query across buy somehow control the list price of the services element controlled elsewhere.
Part of the function of my Db is to produce quotations. I specifically need one table containing line items and will use another table (update query) which will contain the 'quoted for' items. Transfer will be based on numbers of users required. I have the basic idea in mind; i will have a form with drop down to select numbers of users and then some Vb to take records from one table to the other. I can do that fine. The bit I am struggling to structure in my mind is as follows:
In a quote there will be typically three line items
1) Software 2) Installation / Configuration 3) User Training
I can easily do as described above but that relies on the main table being pre-populated with all three line items. however, I'd prefer to have the ability to have prices for daily rates, relating to installation and training, in another table so as to be able to change/update them and not have them fixed in a table.
So I think I would want to have the three line items update query across buy somehow control the list price of the services element controlled elsewhere.
I have a list of events and what the out come was on a particular day or date.
To give you some idea, the event is basically number given to a day and the outcome is all possible things that “could happen” and that did happen (so not all 160 possible Outcomes are used all the time).
So it is quite possible to have:
Event 1 (This could be “Monday”) Outcome 12 (This could be “Rain”)
Therefore as you collect your data over time the “Outcome Diff” would = 12
The problem comes when I try to create a report to automatically calculate the “Outcome Diff” this in a Query as the “counter” that should have been added at the time this was created was not done at the time. I also do not mind if this has to involve creating to a table, as long as I get the “outcome diff” calculated in MS Access, I do not mind how this is done. I also cannot use date diff as event mean more than just an index for the day.
Event, Outcome, Date, Outcome Diff 12, 1, 14-Feb-95, 12 (0 to 12 = 12) i.e this is the 1st event outcome of 1 22, 1, 19-Apr-95, 10 (12 to 22 = 10) i.e this is the 2nd event outcome of 1 29, 4, 07-Jun-95, 29 (0 to 29 = 29) i.e this is the 1st event outcome of 29 34, Ect... *This is just a representation with test data.
Just for further clarification: the reason this is need this is to find out difference in "event" days as opposed to just an average.
My problem is that I would to write a query to display the number of days taken for each of the 12 months of the year.
In the example of Employee 4 their sickness spans two different months, I can't get my head around how to produce the correct result which should look like:-
I have been selected to do a project by my department head. It involves programming an Access database to do various things. It has to have forms and all that good stuff. I'm trying to get the higher powers to get a web server for me to do this as a web application, but I may need to fall back on the Access idea. So I need a good tutorial on programming in an Access environment. I know it uses VB and I am pretty decent when it comes to programming with that language. What I need help with is how to do certain things like close a form using an onclick event or to print a report. There's a lot I need to be able to relearn so if anyone can point me in the right direction I would appreciate it much. I'm doing this for a Commander in the US Navy...(several ranks above me )...so I need to be able to pull this off!!
I have just spent 30-40 minutes reading through this forum. So many solutions. Excellent. http://www.access-programmers.co.uk/forums/images/icons/icon7.gif Smile I am just starting Access and I want to find a course where there are exercises which will take me to a reasonably proficient level.
If anyone has done come across a book CD or online that they would recommend then I’d be most appreciative.
Anyone know a good site w/ help creating charts in Access 2k? I have a few queries id like to display as charts but just playing w/ the chart feature is getting more confusing by the minute. Any tutorial would be great. TIA!
