Queries, Tables and Forms?


I currently have a query and a table which I'd like to create a form for. The query has people's information in it and the table has RSVP information in it (if they need parking, etc.). I'd like to create a form so that I can type in the person's name and then it brings up their address (just to confirm it's the right person) and then i can enter in the RSVP information. Any suggestions
Posted On: Monday 22nd of October 2012 05:56:38 AM Total Views:  252
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Creating 1 report from 2 queries

Iam new to access and database. I have created my tables.queries and forms. I am now creating the reports. In the reports I need to show both quantity imported and the revenue paid on the imports. my quantity is on arrival date and the Revenue is by date paid. how do I run my reports to show both quantity and revenue
VIEWS ON THIS POST

173

Posted on:

Wednesday 10th October 2012
View Replies!

Delete table data based on 3 queries

I have previously only done straightforward delete queries based on a single table. This is the first time I have neded to delete records from a table based on a slightly more complex calculation. Perhaps my method is incorrect and there is a simpler way of doing it, but here is how I have attempted to delete records from my table Im using Access 2003. I have a table of data as follows: Table 1:"tbl_Sales" Store..SalesDate\t.Type\t\t...Amount A\t.31/12/2011..Sales\t\t......100 A\t.31/12/2011..OpeningAmount.150 I want to delete records where Type = Sales and where an OpeningAmount also exists. (I don't want to delete the record containing "OpeningAmount" but the record must exist for me to delete the "Sales" record. To do this I have created three queries: Query 1: "Q_OpeningAmount" A select query with the criteria of OpeningAmount in the Type field e.g. Store..SalesDate\t.Type\t\t\t\t..\t.Amount A\t31/12/2011.. OpeningAmount \t\t....150 B\t31/12/2011.. OpeningAmount \t...\t.160 Query 2: "Q_Sales" A select query with the criteria of Sales in the Type field e.g. Store..SalesDate\t.Type\t\t\t.Amount A\t31/12/2011..Sales\t\t....100 B\t31/12/2011..Sales\t...\t.125 Query 3: "Q_SalesToDelete" A select query joining the first two queries (on the Store and SalesDate) showing records from the Sales query i.e. the records I want to delete: Store..SalesDate\t.Type\t\t\t.Amount A\t31/12/2011..Sales\t\t....100 B\t31/12/2011..Sales\t...\t.125 Query 4: "Q_DELETE" This is my delete query. I join Query 3 to my table (containing the records to be deleted) and display the table data in the query results The SQL for this is:
VIEWS ON THIS POST

161

Posted on:

Wednesday 17th October 2012
View Replies!

Running custom queries

What I'm working on is a form for checking in new inventory items. What this needs to do is - Add a record to the inventory table, for the new item - Add a record to the actions table, showing when the item was checked in and who did it (also, with the ID value of the inventory item) - Fill in most of the fields in both tables with default values. I'm fluent enough in SQL to do this by just executing two INSERT queries, but I see nothing helpful in that regard in Access itself and I can't find anything definitive online to do this. The few things I did find aren't specific to Access and start with OpenDatabase("File name"). Can anyone demonstrate this, or a more proper way to do what I need
VIEWS ON THIS POST

129

Posted on:

Wednesday 17th October 2012
View Replies!

Field totaling queries question

I have a query: SELECT Manufacturer.Name, Products.UnitPrice FROM Categories INNER JOIN (Locations INNER JOIN (Manufacturer INNER JOIN Products ON Manufacturer.ManufacturerID = Products.ManufacturerID) ON Locations.LocationID = Products.CurrentLocationID) ON Categories.CategoryID = Products.CategoryID ORDER BY Manufacturer.Name; It produces a list that looks like this: Nokia $500 Nokia $300 Motorola $200 Motorola $200 Motorola $200 Samsung $100 Samsung $100 etc... What I want it to look like is this: Nokia $800 Motorola $600 Samsung $200 etc... How do I need to change the query
VIEWS ON THIS POST

120

Posted on:

Wednesday 17th October 2012
View Replies!

Linking Table Field data to various queries

I have a Training table containing the following fields: Name, Method, Training Date, Frequency, Trained I would like the "Trained" field to update according to set requirements contained within a query (or however else you may see fit). When today's date (entered by me within a query) is greater than the "Training Date" + "Frequency" field information, I want the "Trained" field to reflect the word "No." Thereby illustrating that the person is NOT trained. I would also like there to be a query linked to that same "Trained" field which states: If a new record is entered into the Training table and that record contains a similar method reference (10.01 vs 10.00), to please place an "O" in the "Trained" column for "obsolete." In other words, if Erin was trained on method 10.00 in 5/5/10 and she was retrain on the newer method 10.01 on 5/10/10, I want the method 10.00 record to reflect an "O" in the "Trained" field column. Finally for all other entries, they would need to reflect the word "Yes" in the "Trained" field. Is this possible Did I confuse the heck out of people with this request Please help if you can.
VIEWS ON THIS POST

183

Posted on:

Wednesday 17th October 2012
View Replies!

Ho to Save password while connecting Database from MS access & how to shedule queries

Hi , Can someone pls help me.. I have a mdb file with some 110 queries in it and a made a ODBC connection to connect to my database and daily at 10:00 pm i ave to run all these queries and sent to higher level. So my problems are... 1) Each time when i click on query it will ask password and every time i ahve to enter a password and hit enter once output comes i have save the output into excel and we have privillage(mean a button) in excel to "Analyze with excel " so all this process takes 2 hrs for me and i know iam wasting my time... 2) Second problem as iam runing this reports daily basis exactly i have to start running this reports at 10:00 PM. So is there any way to shedule this reports so that they can run at 10 daily and save these reports in certain location in excel format. If you have any suggestions pls let me know.. if you want any detail information pls let me know....
VIEWS ON THIS POST

172

Posted on:

Wednesday 17th October 2012
View Replies!

Help on creating a form for queries

i'm new to access so i'm quite lost in creating a query form.. My current form is linked to the query by inputing Like "*" & [forms]![form name]![textboxname] & "*" which is the critera in the query table. But the problem is that the user has to fill up all the fields in the query form before a correct search result can be generated. Is there a way to make a default(eg. to search all options in the field) when the user doesn't enter anything for that field And also, some data are imput in min:secsec format. Are there any suggestions as to what data type i should use for that field Currently, mine is in date/time format. Because the number format cannot input :, but using the date/time data type, i cannot search for a minimum and maximum time. Really sorry if i sound a bit confusing. But i'm really confused, so i much appreciate any help!
VIEWS ON THIS POST

115

Posted on:

Wednesday 17th October 2012
View Replies!

Need help on parameter queries

I'm new to this forum and am looking for help in the following: I'm trying to create a parameter query (using Access 2003) and would like to display just the data that are not null. I tried to insert "and Is Not Null" after the [parameter name] under "Criteria", but it still list null data. Any help will be greatly appreciated (please note that I am a basic Access user and do not do visual basics or anything other than the quick command buttons or the wizard. Many
VIEWS ON THIS POST

129

Posted on:

Thursday 18th October 2012
View Replies!

Updating reports and queries

I am updating an existing distributed access database. I have made changes to an existing report (from my workstation - I have admin rights on the DB). The changes show up on the database. However when a client generates a report, the changes do not show up. What can I do\t
VIEWS ON THIS POST

121

Posted on:

Thursday 18th October 2012
View Replies!

Export data from queries to worksheetclaibc

Using docmd.transferspreadsheet method which worked perfectly in 2003, mostly worked in 2007 but does not work in 2010! Can you help me with this basic task. Used the following: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Queryname", "wr\bhgc\Data\analysis" Transferring 6 queries to worksheets within one file (xlsb) This should then be picked up in another spreadsheet when update links but this also does not work. Also worked perfectly in office 2003 but not in 2007 or 2010.
VIEWS ON THIS POST

91

Posted on:

Monday 22nd October 2012
View Replies!

Macro that shows Start and Stop times of queries

I know there is away to in a macro show the start and stop times of the actions/queries that run in a macro. Could someone please remind me how this is down again
VIEWS ON THIS POST

110

Posted on:

Monday 22nd October 2012
View Replies!

Running subqueries without looking at all the data

I need some help optimizing my queries in Access I have a large amount of data structured in a table as follows: PHP Code: Store\t\tDataYear\t\tDataMonth\t\tAccount\t\tAmount \t1\t\t\t\t2010\t\t\t\t1\t\t\t\t\t\t123\t\t\t\t1000.98 \t2\t\t\t\t2009\t\t\t\t2\t\t\t\t\t\t125\t\t\t\t2421.12 \t3\t\t\t\t2010\t\t\t\t1\t\t\t\t\t\t126\t\t\t\t2414.14 \t4\t\t\t\t2008\t\t\t\t3\t\t\t\t\t\t123\t\t\t\t1311.12 The above table is profit and loss data. Account refers to the financial account. E.g. Sales Revenue, Cost Of Sales etc. I have created a crosstab query to manipulate the data so that it appears as follows: PHP Code: Store\t\tDataYear\t\tDataMonth\t\t123\t\t\t\t125\t\t\t\t126 \t1\t\t\t\t2010\t\t\t\t1\t\t\t\t1000.98\t\t1981.11\t\t1081.10 \t2\t\t\t\t2009\t\t\t\t2\t\t\t\t1981.15\t\t2421.12\t\t9181.16 \t3\t\t\t\t2010\t\t\t\t1\t\t\t\t1811.10\t\t8110.01\t\t2414.14 \t4\t\t\t\t2008\t\t\t\t3\t\t\t\t1311.12\t\t1811.12\t\t1819.09 Using the above manipulation of data, I calculate the profit/loss for each store. For example, Account 123 + Account 125 Account 126. These calculations are done in secondary queries using the crosstab query as the data source. My problem is that when I run the secondary queries, I am usually only looking at a part of the data. E.g. a group of stores or a specific year. However, the crosstab query looks at all the data. Therefore every secondary query I am running is very slow and inefficient. How can I speed the process up For example, if I have a secondary query which looks at stores for 2010 only, at the moment the crosstab has data for all years so its very slow. If I can specify 2010 in the source query then I can hopefully speed it up. But I cant put 2010 in the criteria for the crosstab query because it is also used for other queries where I want to look at other years of data. I hope Ive made sense,
VIEWS ON THIS POST

168

Posted on:

Monday 22nd October 2012
View Replies!

Passing values to queries by form

In my Coin Inventory database there is a category of books that each coin can be housed in. One coin will be stored in one of the books. I created a form (frmSelectFolder) that has a list box (lstSelectFolder) that the user makes a selection to determine which book they want to edit or view. The command button to call the form is disabled so the user is forced to either exit the form frmSelectFolder or make a selection from the list box. Once the user makes a selection the command button is enabled allowing the user to click it. I am calling the form frmSelectFolder from a menu (its a popup box) and the form closes after the command button is clicked. This is what I have working now. What I want to happen is that the command button opens up a form (frmFolderEdit) and the value of StorageID (from the query qryStorage underpinning the form frmSelectFolder) is passed to the frmFolderEdits query qryFolderEdit.. How do I go about doing this
VIEWS ON THIS POST

125

Posted on:

Monday 22nd October 2012
View Replies!

Using queries with date

Hi all, I'm using MS access 07 I'm in trouble, because I need to write a query that take the current date and take off a date stored in another field. How can I do that Is there a formula to update the date everytime the database opens
VIEWS ON THIS POST

66

Posted on:

Monday 22nd October 2012
View Replies!

Adding queries to Access programmatically

I need the c# code to add a new query to an existing Access database programmatically.
VIEWS ON THIS POST

147

Posted on:

Monday 22nd October 2012
View Replies!

Use multiple queries

I have a search menu where the user can choose a search option: 1. Search by Date Filed 2. Search by Paid Date 3. Search by Invoice No. 4. Search by Client Name etc.... I have a query built for each of these options, 14 in all. Is there a way that I could have the user enter more than one option, and link those queries together For example, they could enter 1 and 4 to search for Client Name on a particular filing date. I would rather not have to write a query for each and every option...
VIEWS ON THIS POST

82

Posted on:

Monday 22nd October 2012
View Replies!

Make-table queries for Web?

, I'm using MS Access 2003 for a web DB... Am wondering.. is it bad to use Make-Table queries for the web.. Or should I just do all the SQL joining and linking stuff just with more SQL code Just curious about the impact Make-Table queries have on the Web.. Slower Not a good idea
VIEWS ON THIS POST

117

Posted on:

Monday 22nd October 2012
View Replies!

Linking queries in access 2002

Is it possible to link a query in access 2002 It gives me only tables in the menu.
VIEWS ON THIS POST

100

Posted on:

Monday 22nd October 2012
View Replies!

List boxe for multiple queries

, I have a form on which I have placed a list box which will return data from a query via cmd button. Is it possible to use the same listbox to see different queries, using different cmd buttons I will place multiple button each one of which will run a query that will show its data in the list box. Anyone knows if this is possible
VIEWS ON THIS POST

58

Posted on:

Monday 22nd October 2012
View Replies!

Case select to run queries

Please help with the code below. I am trying to select a different query based on the selection in the [Product] Field. I have 4 different products and 3 different queries to run. Each product is specific to a particular query. What I would like is: When Product 3 is chosen "Query 1" is ran. When Product 1 is chosen "Query 1" is ran. When Product 2 is chosen "Query 2" is ran. When Product 4 is chosen "Query 3" is ran. As it is set up now it appears that when choosing Product 1 I open Query 2 and when choosing Product 2 I open Query 1, but it is the opposite of that. I get the correct query with this code by choosing Product 1 and the Query that opens is actually Query 1, I don't know what is going on here. When I open Products 3 & 4 it always opens Query 2, no matter what Query I specify. Please help me sort this out. I am not very familiar with case select.
VIEWS ON THIS POST

95

Posted on:

Monday 22nd October 2012
View Replies!