In one table i Have component group Id and the description as fields then another table has the sub groupings for each Component Group Id
Example
Component Group ID = 1 which is the primary key
Description = Keyboards
Component
Sub datasheet has Group ID = 1 foreign key
Descriptive Options = USB, Wireless, PS2, Serial
I have joined both of these in a query but what i want is the query when i link it to the combo box on the form i have to only show the Group ID once and not repeat it for as many times as each subdatasheet has records.
Example: When i click External Device I dont want to see the word Keyboard being repeated 4 times (which is because there are four subrecords/types of keyboards) Please Advise.
First of all thanks to everyone who has answered my questions already, although I'm still having problems (more with my understanding and lack of Knowledge)
I'm creating a DB and I'm stuck, I'm trying to create a form that has three fields mailbox, applications & Drives. Each of these three fields need to be populated with many items. For example the mailbox field may have 5 different mailboxes required to be populated in this field.
What I want to happen is for the user to double click on the text box, then a new form containing a multi-select list box appears that you can select the items you need which in turn updates the role profile form. This is how I want it to work but if you have a better idea I'm all ears.
All I want is for someone to be able to view the role profile form and to be able to see what applications, drives and mailboxes are required for that role.
Basically I have no idea how you can get someone to choose several items and have that reflected and recorded in the required record.
I have attached what I have at the moment and any help or advice you can supply is appreciated.
:confused: ok basically im trying to create a form for customer details for a project at college, what I want is:
select customer ID from a drop down list, which then should display all their details below such as address etc(from the linked table), which cannot be edited or nothing, like looking up information. can anyone help me please???
I a a new user to Access having only every ceate a couple of small databases using the wizards. I have just started a database where I have created my tables and I am now creating a form to display information. The problem that I am having is that when in Design mode I can see all the information and fields that I have used to create the form, however when i save the form then opening in display mode I do not see any of the fields, revert back to design mode and hey are all there.
On my form I would like the user to be able to select a company, and once the company has been selected I would like a box to display all the contact information for the specific company. I would also like this box to record the contact information onto my database along with the other information that will be gathered on the same form.The Contacts and Companies are linked with the COSYSNO and CoSysNo fields.
In a Query, how can I display in one column a value that can come from 2 different fields based on a selection.
Say I have two date fields (Date1 & Date2) they can both be blank, or one or both of them could have a real value. To make it simple here is the logic I want to employ. If Date2 > " " display Date2, otherwise display Date1. I want the value to appear in the same column. Can this be done in a query or must it be done in a report? Thanks!
I have a field in a query that I would like to display differently than what is stored in the table. The field is call apt# and the output looks like this 0000000011409468Ø. I would like to display this data without showing Ø. Is there a way to do this? Thanks..
I need to display the results of a query on screen when somebody clicks a button on the form. So I put code in the event of the click that looks like this:
Private Sub Command20_Click() On Error GoTo Err_Command20_Click
So far, so good, but my problem is that this query is one row with a lot of columns - that creates a lot of pages to look through. Is there a command that would loop it around on the same page, or rotate it clockwise so it's going down the page instead of across?
I am attempting to run a query with fields from 6 tables. These tables have been related, but just a simple ID to ID throughout all of the tables. When I select the fields I would like to be in query using the disign wizard it will allow me to create the query, however it will not display and data.
Originally, the query wizard would not allow me to insert all of the different tables fields in the query because "they were not related", so I went back and did the ID to ID relationships. Like I said this now allows me to create the query, but unfortunately no data appears.
I am trying to make a query that includes 3 other queries. The primary key is customer number and I am trying to pull the sales for each customer by week, month, and quarter. If a customer has not purchased anything it does not display at all. Also, if they purchased something in the quarter or month but not in the past week it doesn't display. I would like the sales for the week, month, or quarter to display 0 if no transactions were made. It is not feasible to add a record for each customer with "0" since this would be updated daily.
I have been looking all over for the answer to this and can't find the slightest thing about it.
I need to know how I can query information about what tables are in a MS Access database. Lets say for example I can't access the database with the MS Access application and I want to write a web query in asp to list the tables in the database. For example, I may not know what tables are in there and need to find out.
I know there is a way with mySQL using the "show tables" command but it doen't appear to work with Access
I also came accross some code with OpenSchema(adSchemaTables) that is suppose to do the trick, but it either doesn't work or my shared-hosting provider doen't have it set up to work.
If anyone could PLEASE help me out with the T-SQL query code to get table info I would be greatly thankful!
Every week I download new information into a table. The download has all the information, but the table will spill out any information from the download that is already existing in the table.
How do I print a query or report right after the download, that will provide a list of the new information just downloaded.
Hi all I have a query that is getting data fron 2 linked tables. The relationship between these 2 tables is 1 to many. The first table(One side) contains Due Dates for services. I have asked the query to return services for the current date which is working fine. The second table(Many side) has additional work required for each vehicle. The work can be either New, On Hold, In Progress or Completed. I only want work that has not been completed to be returned if the vehicle is due for a service today. I tried having the Work Status field in the query and criteria set to <> Completed. This returned all open work including vehicles not due for service. Is there anyway this can be achieved. If I take the criteria out of the Work Status field I get the information I am looking for but it includes Completed work and I do not need to know this. Any and all advice appreciated.
I have 3 separate pages that display information from 3 separate queries, all from the same table.Queries 1 is on page 1Queries 2 is on page 2Queries 3 is on page 3My problem is I can't edit the information and I cant figure out how to make it editable. From what I read it should be. Can anybody help me?
I have 2 tables setup with a 1 to many relationship, simialr to 1 Customer having 1 or more orders. I created a query to prompt for all orders for a particular customer. I then created a form based on this query - i.e. linked the query to a form.
Now when I open the form I am prompted for the customer id and when I enter this all of the results of the query are displayed in 1 form. However, this is not what I want. I would like the orders for the customer to be displayed within a subform on the actual form. In this way the customer details will static and I can cycle through the customer orders in the subform separately.
Is there any way I can setup my query to display the results in this way? I need the prompt for the customer ID. Or is there any way I can setup the form to display the results in the above way?
Appreciate some help on a update query if possible. In this query I wish to total two sets of figures in about five seperate queries and then export them to another DB. the Dsum function is fine and will produce a result but that result needs to pick up the record ID (called PlanID) which is the primary key for the records being updated. I have struggled with this a bit as no matter what I did the PlanID field would not even display in the query results.
The section of code that is highlighted will now display an empty field and as such the rest of the query is blank. If I manually insert the PlanID figure into the record that the query is updating the other figures will appear.
How can I change this query to add the Planid to the update after it gets it from the customers form?
I run a Query based on some data in a "drop-down" box. It works in just datasheet view, but when I print it on a report, it displays the position that the data is in the drop-down box. How do I display the data, not the position on the report? Here's an example: My drop down box contains: apples oranges bananas
My query returns apples, but in the report view it displays the number "1" instead of my data "apples". If the query returned oranges, the report would display "2" instead of "oranges" and so on.
I have created a Query to search for clients in my database, the query and the search all work fine, however it would be nice to have the query post the results into a form I have created to display clients data rather than posting the results in a tables view.
Hi, I'm building a form where the user can choose various options to build up the query they want. It's going to be easier this way because the different combinations they might want would mean me writing well over 100 different queries. So I think I can build the sql statement in vb and then get a recordset back with the results, but the question is how to display it. I can't just assign the recordset to an existing form because the fields to be displayed will be different for different options. Ideally I'd like it to open just as if I had run:
DoCmd.OpenQuery <name of the query>
But it doesn't look like this is going to work for a query I've generated in the code, even if I use a querydef object (correct me if I'm wrong?). So does anyone know how I would go about doing this? (It would also be fine if the results appeared in a subform on the form I'm calling the query from, but I can't see how to do this either).
Forgive me for what may sound like a dum question -- Access is completely new to me.
I have my table. I have this displayed as a form.
At the bottom of the form, I have a text box and a command button.
The user enters in a key value. The command button triggers a macro that runs a query.
Now, I'd like the form to display the data returned from the query. How?
I tried creating a new form with its Record Source set to the query. I had the macro close the original form, run the query and open the new form. But before the new form could be opened, Access prompted me for a 'parameter value' (I assume so that the query could be run again).
I searched the archives and Access Help and couldn't quite find the answer to this question, which is no doubt simple.
I have a form showing Projects with an embedded subform showing all the Buildings associated with a given project. In the form footer section of the subform, I would like to display summary information about the buildings in the project being displayed, such as count of buildings, count of residential buildings, count of commercial buildings, and so on, and I would like these summary values to update as users add or change building information.
I know how to write the SQL query to pull this information, but do I use a text box or some other vehicle to do this? Using a text box as a calculated control, I was able to produce the count of buildings using:
= COUNT([UnitID])
To get the count of residential buildings, I need to be able to specify "WHERE bldgtypeID = 'R'"...I can't get the text box to display results of an SQL statement. Can text boxes handle SQL statements?
I have a query which totals the amount of all invoices for a specific project number. I would like to take this total and have it displayed on the form from where overall project information is inputed.
Right now I have a table which shows general project information and a table showing invoice infomation (linked to the general table via a project #)
I also have two forms, one for general info and there's a button which opens the invoice form. When the invoice form opens, it opens an invoice with the corresponding project number shown on the general form. I would like the total invoice amount (from the query) displayed on the corresponding general form.
ex: in the general form for project# 100, I click invoice once and enter $50, I click invoice again and enter $100. The query automatically shows a total of $150 for project# 100. I would like this $150 displayed on the general form for quick reference.
