hi i need some advise here. I know a little about Microsoft Access and I am trying to organise a database.I have a table called receipts with column of firstname, last name,total cost, deposit, receipt, receipt#, receipt date. I did a calculated field in query to find balance due. My problem is adding all the receipts recieved from an individual to subtract it from the total cost to get the correct balance due by each individual. (Remember all receipt on the same table)
Could someone tell me how to organise it to get balance due.
This should be a simple one but I still can't manage it.
I have a table on which my records are organised in alphabetical order by one field.
On my form which uses this table as the record set the records are all over the place and it is a nightmare finding the record I want. I don't understand why it goes out of order in the form.
How do I make sure that they are organised using the same field?
Hello all. Would anyone be so kind as to help me out with this beginners problem. I have a lot a books which i'd like to sort into categories. I'd like to be able to select a category from a combobox on a form, and for relevant books in that category to appear in a listbox. Upon clicking on a particular book on the listbox, relevent info should appear on the form eg. date purchased,author etc. Is it also possible to have an option to view all books from a particular genre in spreadsheet format embedded in the form, and an option to view every book regardless of genre. I'd then like to be able to print reports on each/all. I'm sure i'd be able to manage if someone could kindly get me on the right path with an example.
Hi I have a large database with many tables, forms, queries repost etc. These are stored named and displayed alphabetically, is there a way to place them in folders within access so it is easier to organise and locate as i am developing. Or do i just need to rename them all with a section title as the first part of the name?
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items. How can i make 3rd query which will give me all but taken items from query1? (of course items from query 1 are in query2) thx in advance
How can I pull the last two values, grouping by property id, so I can compare them? I can use Max to get the last value but not sure how to get the previous one too!
This may be a simple problem, but I cannot find the solution myself.
I need to design a query based on a table describing all sales during a year to a number of clients of my company. However , even if 50 sales may have been made during the year to some clients, I want the query to return only the top 3 sales for each client.
Hi Guys I'm not really sure wether this should be in queries or forms (as not sure where the code needs to go) but heres the problem. i have a front end form (where the user has various drop down boxes to choose from) which then runs a query and outputs the results in another form.
At the moment when there are no results it just brings up a big grey empty box, which is annoying since I want to use custom close buttons and disable the red x. Is there any way that if there are no results the query could open a different form (where I'd put some kind of error message and navigation choices).
I made a form with a specific field. If I enter a value into this field it automatically runs a query. The results are shown in another form. However if I return to the previous form and enter the same (or another) value it still runs the query but this time with no results? I think it is a memory problem. The previous form is closing.
I'm trying to run an SQL statement to Sum the values of a table column. No problem. It would be useful for me to take that total and be able to set it equal to a variable within my VB code that's running, I just don't know how to do that, I'm very new to VB programming.
I cannot find help anywhere on the internet for this. :( I'd like to do something like this, just don't know how:
VariableName = Run.SQL ("Sum(tblName.Column) AS SumOfColumn FROM tblName")
I'm not a programmer by trade, so I am picking up things piece by piece, and just cannot find my way out of this task. Thanks for your help guys.
I'm doing a table for Future Business Leaders of America competitive events assignments.
I want the same table to hold Name and Event Name and be able to sort
However, I want the database to be able to sort to tell me each event that a person has, and at the same time, how many people are in each event. Presently, I have to switch between databases.
On one table, the event name is the primary key, and on the other the person's name is the primary key.
For the event name table, categories are "Event Name" , "Participant 1" , "Participant 2", "participant 3", etc.
For the Person's name table, Categories are "Name", "event 1" , "event 2", "event 3", etc.
I want to have ONE database that will sort lists for all participants in each event AND that will sort into a report or form so that I can inform participants of their events.
How can I do this?
I probably was not very clear, so ask lots of questions if you're unsure.
Im having a real problem in creating a query to return the results that I'm after. I have a wargaming Database. Units are created on seperate forms i.e Armour, Infantry etc. There could be a number of Battalions of each say Armour1, Armour2, Infantry1 Infantry2, the amount is endless. All of these battalions are linked to a game name i.e Battle 1, Battle 2 etc.
Im trying to show in a seperate table the name of each battlion that is involved. At present whaen I create my query I use the Arour Table and the Infantry table but if Ive only created 1 Armour unit and 2 Infantry unit, it shows the record twice. ie:
I am wanting my query results to appear in my main form so that i can edit them, rather than a table the query is just a look up of my form that stores about 500 records and stores all the same fields
Below is a query were I can get eveything into tow columns but I would like to have the results in one column?
SELECT tbl_Admin_Company_Details.Companyname, tbl_Admin_Company_Details_1.Companyname FROM tbl_Data_JobOrder INNER JOIN tbl_Admin_Company_Details ON tbl_Data_JobOrder.CompanyID = tbl_Admin_Company_Details.CompanyNumber, tbl_Admin_Company_Details AS tbl_Admin_Company_Details_1 INNER JOIN tbl_Data_Receival ON tbl_Admin_Company_Details_1.CompanyNumber = tbl_Data_Receival.OwnerID GROUP BY tbl_Admin_Company_Details.Companyname, tbl_Admin_Company_Details_1.Companyname;
The Results are;
tbl_Admin_Company_Details.Companynametbl_Admin_Company_Details_1.Companyname Company 1Company 1 Company 3Company 2
What I would like is something like Company 1 Company 3 Company 2
Is it possible to change the resulting format of a new field (expression) within a query? I.e. Instead of the resulting when running query being displayed results in decimal places but instead being displayed in percentages
I've created a database to keep track of all our Microsoft licences for the company. Just to make sure we're fully licenced, to allocate where needed, and to help reallocate when we replace a pc.
I have 2 tables, 1 hardware (listing PC ID,location etc.) and 1 software (listing software type, software licences, and to which PC ID allocated).
What i'm trying to do is filter the pc's missing either an Office licence or a Windows licence or both, so i can base a report on the results.
I have two database tables, one of information of members and one of staff. I want to do a query to show all members and staff that have the title of 'Mr' in the database.
There are only 5 staff details in my staff database at the moment, all with the title 'Mr'. There are 20 members details, 13 with the title 'Mr'. When i run the query i get only 8 names, 4 from the staff and 4 from the members.
The 4 staff ones i get are the 2nd, 3rd, 4th and 4th records. The first record of the members field does not have the title 'Mr' and so it appears that this is blocking the first staff one to appear. And the members stop appearing after the 5th record too, so it appears they are stopped as there are no more staff records.
I hope this makes some sense to you, and if anyone could tell me how to get all the records to appear that would be great. Thanks.
Is there a way to write one query that will work for different criteria and is run by different buttons to produce bar graphs or a basic report depending on the button the user clicks??
I am working on a query but can manage to get it to do what I want. I have a main table with a job_ID and a faults table which documents faults that occur related to that Job_ID the tables are related with a one to many relationship. I want the query to bring up all the jobs even those with no faults, at present it is only bring up those jobs that have faults, how do I include details of all the jobs and if there are no faults then that field is left blank, probably very simple to do but after working on this db for hours my brain is no longer functioning!! :eek:
I have a query which is made up of several other queries that total different fields (these are a base for a report) sometimes one of the queries will be empty and then the whole query is blank, how can I return 0 when there are no records to count in 1 query so that the results from the others still show?
I have a existing database that I use for pulling data out of our companies data warehouse and I have noticed that when I run the query it is pulling information more than once. So I'll have 13 extra entries which then screws up my reports.
I have a table called tblODF and within that table, I have these fields: - ODFNumber - ODFScanDate - Status (Combo box: Pending, Complete, Licensing) - LastFollowup
In my query, I have the same fields, except I added a calculated field. I added DayCount to
Codecalculate:(Date()-[ODFScanDate]
This should calculate the number we've had the ODF.
What I want to happen is, let's say the status is Maturity, I want the DayCount to say 0. If it's pending, I want it to calculate the actual days.
