Queries :: Select Only Once A Field From Normalization Sheet Table
Sep 29, 2014
I am using this query to cluster a group of customers by monthly revenue. The issue is that I get duplicates. The customers appear more than once because the condition is valid more than once. How do I get access to select only once a field from Normalization sheet table??
SELECT [Cust Profile This Year].MSISDN, [Cust Profile This Year].[Total Revenue], [Normalization sheet].[Arpu Range] FROM [Normalization sheet], [Cust Profile This Year] WHERE ((([Cust Profile This Year].[Total Revenue])>=[Normalization sheet].[Arpu]));
Let's say Table (T1) has fields F1 and F2. After a massive update to T1, there are some records with F1 = "" because a Dlookup using F2 as criteria to another Table (T2) resulted in a null. I created a select query to show unique T1F2 values where T1F1 = "". The user can use this query to find out which F2 values need to be added to T2.
How do I create an update query that will update T1F1 with values from T2 using the T1F2 results from the select query to again use the Dlookup to T2 (of course after T2 has been updated to contain the missing F2's)?
I'm not a programmer and know very little about setting up access databases other than the very basics...however...due to my politics background, I'm setting them up for the local government....just my little disclaimer about why I'm asking stupid questions.
I am creating a database for multiple users who are of two sorts. The users deal with either the setting up of a contract, or with the payment of a contract. Both of these groups need to be able to access different information about the same contracts. What seems most logical to me is to create two tables in the same database, one for the contract, and one for the payment information and have both update when one or the other is worked on.
My basic set up for now is this: Table One: contract information Table Two: Payment information Link Key: Serial Number from contracts These are my questions:
1) is there a way to set this up so that the only way someone from the payments group can create a linked record is if a record with the same serial number already exists in the contracts table? 2)Is there a way I can set it up so that when someone in the payments group starts to input their information, they can find the serial number they want, and have certain fields show up? For example: I want them to be able to search for a serial number and have the contract name show up with all of the previously entered payment information, then have a new field each time they go into it for their next payment (if that makes sense). 3) When linking, hopefully in such a way as to do the above, do I need to have the same field name in both tables, or...? I'm a bit confused as to how this works as initially i created a field in both tables that would have the same serial number and tried to link those..and it didn't work.
Sorry if this is so confused...I'm sort of learning as I go along.
I have a contract-tracking database that has been in use for about a year. It used to be that every contract had the same number of periodical "applications" with it - the companies we worked with would send in 4 applications over time.
So, I had this set up:
tblCompanies (the companies with whom we work) - CompanyID (Primary Key autonumber) - CompanyName - Address, contact info, etc.
tblContracts (the contracts we issue) - ContractID (Primary Key autonumber) - CompanyID (foreign Key) - DateIssued
tblApplications (things companies send back to us over time) - AppID (Primary Key autonumber) - AppDesc (description of the application - something like "Application 1", "Application 2" , etc.
tblAppsRecvd (Join table/log of apps received) - ContractID (foreign key to tblContracts, so we know which contract this applies to) (also a composite primary key, so we don't duplicate an app to a contract) - AppID (foreign key to tblApplications, so we know which application we rcvd) (also a composite primary key, so we don't duplicate an app to a contract) - DateRcvd
Pretty self explanatory. Everything was keen, and I was reaping the rewards of a job well done.
But now we've expanded out contract offerings. He have introduced 2 new types of contracts, and each contract has a different amount of applications associated with it.
Contract 1 has 4 apps. Contract 2 has 2 apps. Contract 3 can have 1 to 8 apps. (The last app before a contract closure is always considered "Final" - so if we get 5 apps, it would be apps 1, 2, 3, 4, and Final.)
I've tried implementing a couple different schemes, but none seem to be fitting the bill. The type of contract will dictate how many apps we receive - i.e. If we are using Contract 1, then we KNOW we'll recieve 4 apps over time. So, is this as simple as adding a "ContractTypeID" to the tblContracts with an appropriate tblContractType table, and expanding tblApplication to more apps, or is there a smarter scheme?
Im setting up a database that contains information about printed circuit boards that are being developed for our current project. The information that i need includes the following:
Board # - 3 digit number Board Name - Module # - 9-digit number Module Name - Project Leader - engineer responsible for design In/Out/Oem - inhouse, outsourced, or purchased OEM Designer - engineer or outsource contractor who designed the board MFG/Assembler - company responsible for building the boards Directory - file on network drive the drawings are located in
There can be mutiple boards in a single module. So i have 3 tables right now.
pcb_tbl - this includes all the info above minus the module info.
Module_tbl - this table includes the module number and name and responsible engineer, with the module number being the key.
module_pcb_tbl - this table lists the module number in the first field linked to the module table, and the pcb number in the second field linked to the PCB table.
I have a simple modeling question. Lets say I have the following table structure:
Table 1 ------- Cust_id Cust_name Phone City Supplier_id Dept
Table 2 ------- Supplier_id Supplier_name phone City
Table 3 ------- Dept
Table 1 is M-to-1 with Table 3. Table 2 is 1-to-M with Table 1.
I have seen such as schema and do not understand why there needs to be a separate Table 3. Can someone explain why Table 1 alone would not suffice and Table 3 would not be required?
Basically, can someone explain the rationale for normalizing tables when there do not exist sufficient associated data for the field for which a new table is created (i.e. in this example, if Dept had a lot of other fileds associated with it such as Dept_desc, Dept_Mgr etc , then I can see the value in creating a different Dept table and joining it with Cust table through a Dept_id field).
A similar such design exists in the standard "Address Book" sample schema that ships with Access (the analog field in question there is Role).
I am having trouble with the table design of my db.:confused:
What I am is a human resource supervisor trying to develope a database to track the hundreds of actions that come in and out if my office on a weekly basis.
I am almost there, in regards to table setup and relationship setup, except I am stuck on something and any advive I can get would help tremendously.
Here is what is going on:
I need to be able to relate my customers to the actions they turn in..and track them, the actions, until complete.
I have attached my db and will answer any questions needed. Will someone please take a look at it and evaluate what you may think is needed to get the "ball" rolling.
Here is where I get lost. Each item has a variable for each month So item 1 January = .01 February = .03 ECT. Once set the Variables rarely change, at most once per year.
I will need to be able to pull an item and based on what month it is pulled uses the variable to multiply several other factures.
My first Thought was,
tblVarItemList IDVarList FKToItemList VarJanF VarFebF VarMarF --And so on--
It just does not look right. Any Suggestions on what might work Better. :confused:
I have attached a screenshot of the relationships in (a section of) a database I am working on. Would anyone please be willing to view it and offer any suggestions as to whether is fully normalized or not? Here is the description:
An account [table, Account] consists of some money. That money is split-up and invested into several different places. The funds that are available as choices are in Fund; the funds that are chosen are in InvestmentInstance. Different, unaffiliated accounts can invest in the same fund. I would like to keep track of when an account begins to invest in a fund and when it stops investing there (“InceptionDate” and “CloseDate”). The generic, account-independent performance of a fund is kept in Price, where the values come from Yahoo! Finance or something like that. Similar to Price, MarketValue stores the dollar-amount of an investment, specific to an account.
I think I have most of it set OK, but my main concern is about the relationship between InvestmentInstance and MarketValue. As the picture shows, I use InvestmentInstance’s “Id”* as the “one” in the one-to-many relationship, but “Id” is not a primary key. To get it to work, it was necessary to set the Index option to, “Yes (No Duplicates),” so that it was a unique index. Is this a bad practice? Should I just include “Id” as part of the compound primary key? I didn’t think making it part of the compound key was a good idea because then it would defeat the purpose of having “FundId” and “AccountId” control the uniqueness of records. Am I missing something important?
In other words, how can I correctly create a one-to-many relationship that begins in a juntion table (InvestmentInstance) and connects to a new table that was not involved in the normalized many-to-many relationship (MarketValue)?
Thank you!
* I know many like to include the table name in the ID, like, “InvestmentInstanceId,” and it is only my preference to keep it just as “Id” for a local key for simplicity, but add the table name when it becomes a foreign key. I feel “Id” is good enough for my purposes and it’s table location is usually clear from the context.
Im planning a db client's dinner questionaire that will comprise of client's favorites (meals, games, transportation). Each client is allowed to bring a guest. So the client's will also be filling out "the same set" of questionaire for his/her guest.
Meaning, a user will need to fill out the same form twice, one for himself and one for his guest.
Now, my question is, should i have two tables. Client's questionaire table and Guest questionaire tables with foreign key in the guest's table showing who's guest the question belongs to?
Or should i have one table for everyone (since they are the same set of questions). Having one table seem like a wise idea since it's the same set of questions (don't want to have 2 duplicate tables with only one field different). Now, if i go with one table. How do i go about handling this? So that client's and guest's info are linked. and i can run a query to tell who's guest it is? I have never tried thsi before. Care to share your thoughts?
I'm trying to allow my users to do some analysis of existing data. they would like to be able to generate a report which will summarize sales orders against the month they were placed. They would also like to summarize this by between 1 and 4 other fields.
I have created a "filter" form to allow them to choose the values for each of the 4 fields and my plan is that they will leave them blank if they do not want to narrow them down. My problem is that I would like to remove the grouping on the field if they do not select a value for it.
Example data:
Field1, Field2, Field3, Field4, Cost, Month, Year 1,2,3,4,£20,4,2014 1,3,4,5,£10,4,2014 1,2,4,5,£5,4,2014
If they select nothing for any of the fields I would just want to see that my total for April 2014 is £35
If they select 1 in Field1 then I want to see one line with a total of £35 (i.e. it ignores the distinction of the other selectable fields)
If they select 1 in Field1 and 2 in Field2 the total would be £25
If they select 2 in Field2 and 4 in Field3 the total would be £5
etc. etc.
I am struggling because I need the distinct records to allow me to group on them when I need to but if I don't need to group on them I need to ignore the grouping.
i have a main form named(EMP) i have a subform named(SEMP)with EMPID i have an another form Named(SDetail) with EMPID i want to open form Sdetail with filter records for data select in subform (SEMP) ,EMPID field Subform SEMP in as datasheet view. i can open sdetail for selected records only
I have three tables. TableA contains certain order information, such as the username of the employee, type of order, alias number, etc. TableB contains phone numbers, username of the employee, etc. TableC contains supervisor names and alias numbers.I didn't make these tables but that's what I'm working with. I connected TableA to TableB with the username of the employee, and then TableA to TableC using the alias numbers.
I have a report that summarizes how many orders of each type there are. It shows the supervisor, employee username, alias numbers and then each type of order with the amount of orders of each type under the headers.My boss wants to be able to click on the amount of that type of order and have it show him the details for those orders. This is where my problem is.
I was able to use an Inner Join type of query that allows me to filter the data by order type (however, I have to make a new query for each order type since I'm not too experienced on this). The problem is that I cannot find a way to have a query filter the data by order type AND employee username, so that it only displays the orders taken by a certain employee and it only contains one order type at a time, depending on which order type you clicked.
I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :
SELECT [tblLink].[fld1] AS Field1, [tblLink].[fld2] AS Field2, [tblLink].[fld3] AS Field3 INTO [tblTemp] FROM [tblLink]
(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)
Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)
I tried this :
SELECT [tblLink].[fld1] AS Field1, [tblLink].[fld2] AS Field2, [tblLink].[fld3] AS Field3, False AS Field4, False AS Field5 INTO [tblTemp] FROM [tblLink]
But this sets Field4 and Field5 as Number fields, with each record given a value of 0. What syntax is required in the SQL to make these fields Yes/No rather than Number?
I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.
It mostly works in just trying to make it more functional.
Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).
The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.
I would like to update the names list box based upon which cities and locations are selected (both are multi-select).
Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID
The query looks like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO ORDER BY t_asset_personnel.LAST_NAME;
And the list box wizard hides USER_PHY_ID
By setting the width to zero
If I make my own query in the City_After_Update()
Can I do something like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID .fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration ORDER BY t_asset_personnel.LAST_NAME;
I'm new user converting over from Lotus Approach. I'm using Access 2013.
I have a database with two tables being Suppliers and Products. This is a one to many type database with Suppliers being the one table.
Both tables have their own data entry forms.
In the Products table and data entry form is field named Category with a value list of about 20 different values. The query I need to create is to search on the field value from one of the listed values in the associated combo box.
I have set up the basic query using the Query wizard. To run This I have to entry the value I wish to search for in the Query in Design mode.
My problem is now I want to drive the query from a new form. On the new form I have created a combo box where the field list is the same as on the product data entry form.
I need to use this combo box to select the Category field value I need to search for and then click on the button to run the query. I seem to be stuck on the SQL code to achieve this...
On [Form1] I have a Multi-Select Listbox[List1] which shows Job Numbers [WBS] (The job numbers displaying are a result of a separate query filtered by the Fiscal Year combobox). When I click [CMD1] I want [Query1] results to only be selected [WBS] from the form.What is the best way to code this? I'm a beginner when it comes to VBA.
I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute. When the date/time stamp is > 1 minute the record can be deleted from the table.
I have a UNION of two SELECT queries. It works very well but I need the end-user to be able to modify the data so I am using INSERT INTO tablename. This will work for one query at a time but if I try to use the UNION it reports “Syntax error in FROM clause”. Can you use UNION in this case or can anyone see my error?
The full statement is rather complex, I have added a few spaces and linebreaks to make it readable as follows:
INSERT INTO TableCompleteForMailing
SELECT [Address List].[FamilySurname] AS Surname,
[Address List].[DEARFirstnames] AS FirstName,
[Address List].[Mailing] AS Mailing,
[Address List].[Christmas Mailing] AS OtherMailing, [Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Address List]
UNION
SELECT [Names].[LastName] AS Surname,
[Names].[FirstName] AS FirstName,
[Names].[MailingList] AS Mailing,
[Names].[Selected] AS OtherMailing,
[Address List].[Address 1] AS Address1,
[Address List].[Postcode] AS Postcode
FROM [Names],[Address List]
WHERE [Names].[AddressListID]=[Address List].[AddressListID]
I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
I have a query with multiple fields that is being run off of 3 parameters (linked for selection in a form). The problem is, I wanted to enable a select all feature, so I included a "Or ... Is Null" part in my criteria section, so that when nothing is selected, the query/report returns all records.
Okay so the problem is whenever I run the query with nothing selected for the parameter and then return to design view for the query, a new field has been created in the query design, titled with the expression I use to pull the parameter value from the form. This is frustrating because then that is causing errors in another report I run that pulls values from that query.
While I am not new to Access, I am not well versed in its abilities as far as combo boxes go. What I have is a Form where a combo box allows you to pick from a table records 'record ID #' in order to fill in the data of that record to the rest of the form.What I want to do is use a query to select specific records from this table and allow the combo box to show only these 'selected Record IDs' for user selection.
