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)!
Regards and thanks
Mack
View Replies
ADVERTISEMENT
Jan 24, 2007
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.
View 5 Replies
View Related
Jun 4, 2005
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?
View 1 Replies
View Related
Jul 22, 2005
I was using this form tutorial to password protect my administrative tools. When I went to run it I received this:
"User defined type not defined" and the line Dim rs As DAO.Recordset was highlighted.
I have little experience with VB and I was wondering if someone here had any idea if it was their code that was wrong, or if it was something I did...
View 7 Replies
View Related
May 5, 2005
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.
thank you
View 5 Replies
View Related
Oct 24, 2006
Hi,
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?
Thanks for your time
View 5 Replies
View Related
Dec 31, 2006
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.
Frank
View 3 Replies
View Related
Apr 21, 2005
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?
thanks alot in advance
chris
View 4 Replies
View Related
Nov 18, 2006
I would really appreciate some help with this:
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?)
View 10 Replies
View Related
Mar 24, 2006
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.
View 2 Replies
View Related
Apr 1, 2005
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.
Any suggestions/thoughts please.
Thank You
View 12 Replies
View Related
Jun 3, 2007
Hi,
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:
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
11/05/07
11/05/07
rather than just one date:
12/05/07
11/05/07
10/05/07
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.
View 5 Replies
View Related
Dec 2, 2007
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
View 1 Replies
View Related
Nov 3, 2007
I have designed a query with the following fields.
Date;
NumberofDaystoCallBack;
CallBackDate: [Date]+[NumberofDaystoCallBack]
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.
Any help or suggestions? :confused:
View 13 Replies
View Related
Mar 28, 2007
Hi All
Im after some help with a query im building.
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.
Please can anyone help?
Many Thanks
Dazstarr
View 5 Replies
View Related
Nov 3, 2007
Good evening all!
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.
Any advice? Many thanks.
View 1 Replies
View Related
Nov 3, 2007
Good evening all!
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.
Any advice? Many thanks.
View 4 Replies
View Related
Jan 15, 2008
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.
View 1 Replies
View Related
Feb 4, 2008
Hi all,
I have a table of employee sickness / absence records with the following structure and data:
http://www.geocities.com/cyngorsir_ynys_mon/TBL_SICKNESS.gif
I have figure out how to write a query which displays the number of days taken for each sickness (END_DATE minus START_DATE).
http://www.geocities.com/cyngorsir_ynys_mon/QRY_DAYS.gif
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:-
http://www.geocities.com/cyngorsir_ynys_mon/OUTPUT.gif
The only way I can think of doing it is by recording each day of sick individually rather than just the start and end days?
ANY input / comments / observations would be greatfully received!
Thanks
View 3 Replies
View Related
Oct 20, 2006
Hi,
I am trying to learn how to use SQL. Can ayone please tell me where to download/view a comprehensive and easy to use tutorial?
Thank you very much.
Gareth
View 1 Replies
View Related
Jun 4, 2005
Does anyone knows a good VBA for Access tutorial or documentation i can download so i learn how to write the Event Procedures in MS Access?
Thank you for your help.
View 1 Replies
View Related
Jan 26, 2006
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!!
Thanks much,
JMH
View 1 Replies
View Related
Sep 21, 2005
Hi All
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.
All the Best
Jacky Kenna
View 1 Replies
View Related
Jul 5, 2006
Hi
I am new to Access and would like to learn by taking on small projects.
Does any body know any good tutorials for beginners that could show how to start a database for a list of customers or something similar?
I have looked at the ones that can be created with the Wizards but obviously this doesn't help you understand the mechanics.
I would like to learn the 'nuts and bolts' so that I can expand the database and create new ones the more advanced I become.
Any good suggestions would be appreciated.
Thanks
View 2 Replies
View Related
Apr 17, 2007
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!
View 1 Replies
View Related
Nov 11, 2007
Sorry, couldn't let the world go without these instructional videos. It's not much, but it's a good place for beginners to start:
Access Instructional Videos
This might be a good sticky
View 11 Replies
View Related
