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]));
View Replies
ADVERTISEMENT
Sep 23, 2014
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)?
View 1 Replies
View Related
Nov 7, 2012
How to export ms access table into excel workbook with separate sheet based on a value of field?
For Example:
I have One Table with three fields
Name Address Company
Steve a Apple
John b Apple
Josh c Dell
Pete d Dell
Pat e HP
Jacob f HP
Output in Excel(list for Employee by company):
Sheet 1 Sheet 2 Sheet 3
Apple Dell HP
Name Address Name Address Name Address
Steve a Josh c Pat e
John b Pete d Jacob f
View 3 Replies
View Related
Nov 20, 2007
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.
View 6 Replies
View Related
May 16, 2005
Hey buddays,
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?
Thanks, buddays!
View 4 Replies
View Related
Sep 15, 2006
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.
Am i doing this right?
View 3 Replies
View Related
Oct 31, 2005
Hello.
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).
Thank you for your response.
View 3 Replies
View Related
Dec 4, 2005
Greetings-
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.
Thank you for your help
DW
View 1 Replies
View Related
Apr 25, 2005
I am having trouble visualizing how to break out the following info into functional Tables
I have a table to hold Items
tblListG1Items
IDItemList
ITemNumber
ItemName
I then have a detail table
tblDetailG1Items
IDItemDetail
FKToItemList
ItemMFG
ItemMSP
ItemLead
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:
View 6 Replies
View Related
Apr 8, 2008
Hello,
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.
View 8 Replies
View Related
Jun 16, 2006
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?
View 3 Replies
View Related
Sep 4, 2014
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.
View 6 Replies
View Related
Nov 11, 2013
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
View 14 Replies
View Related
Mar 1, 2015
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.
View 14 Replies
View Related
Nov 28, 2013
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?
View 4 Replies
View Related
Jun 4, 2013
How to query Select Distinct field + showing other fields? E.g.:
Code:
SELECT DISTINCTROW assetMovementTable.assetNo, assetMovementTable.moveCode, assetMovementTable.compCode, assetMovementTable.compCodeDesc, assetMovementTable.assetDesc, assetMovementTable.equipType, assetMovementTable.equipManufacturer, assetMovementTable.equipModel, assetMovementTable.constYear, assetMovementTable.plateNo
FROM assetMovementTable
[Code] ......
View 7 Replies
View Related
Jun 11, 2015
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;
View 7 Replies
View Related
Apr 11, 2013
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...
View 2 Replies
View Related
May 16, 2013
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.
View 12 Replies
View Related
Jun 26, 2014
I can not solve this problem, my Access knoledge is mediocre.
I have the following table from imported data:
RecNr IPaddres DateandTime
1 178.00.000.01 20-11-2013 21:47:21
2 178.00.000.01 20-11-2013 21:47:59
3 178.00.000.01 20-11-2013 21:48:35
4 178.00.000.01 22-11-2013 20:44:59
5 178.00.000.02 22-12-2013 19:47:59
6 178.00.000.02 22-12-2013 20:47:59
7 178.00.000.03 01-02-2014 12:47:59
8 178.00.000.03 01-02-2014 12:48:30
9 178.00.000.03 01-03-2014 11:47:59
etc
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.
View 6 Replies
View Related
Mar 29, 2005
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]
ORDER BY Surname, FirstName;
View 3 Replies
View Related
Nov 27, 2013
I need to give the user the ability to select either all of a table say
[tblStores].StoreNo
or only those stores where [tblStores].StoreNo Is Null
I cant for the life of me get my query to accept Null or Is Not Null in the parameter box.
What do I need to do.The end game is to produce a subform to allow users to maintain tblStores
eg
tblStore
StoreNo | StoreNm
000001 | StoreA
<Empty>| StoreB
000003 | StoreC
Null returns
Store B
Is Not Null returns
StoreA
StoreB
StoreC
View 2 Replies
View Related
May 11, 2014
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.
View 3 Replies
View Related
Jun 16, 2015
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.
View 1 Replies
View Related
Nov 14, 2005
Hi
I need to select from a table field of upto 50 characters: the first 15 characters and then the remaining charachters (i.e. after 15...)
I would much apreciate some help...
Thanx
View 9 Replies
View Related
Dec 14, 2013
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.
View 1 Replies
View Related
