Relationships And Wrong Data
Aug 20, 2007
I have 2 excel sheets that I have imported as external data. I created a relationship between the 2 tables on part# field that is common to both. The relationship is to take all info from A table and only those from B table that match. The part# field is set to text in both tables. When I run a report that merges the 2 together a significant # of parts in my field change to a negative number. For instance the part# in both tables are 12345678 it is changed to a random -3452345 number..
Any ideas?
Please help
View Replies
ADVERTISEMENT
Jan 4, 2006
I am not sure how this query I made is pulling up wrong data. Everything else is fine, except for 1 row of data. It is supposed to pull how many demonstations were scheduled for that day and how many were executed. It comes up with 4 demos and 8 executed. That can't be right. I checked it with the actual data and there are 6 demos total and 4 executed. Anyone know what could be going on there? For the other 30 something records it works just fine.
SQL:
SELECT [Promo count].PromoNo, [Promo count].[# of Demos], Count(DemoOrder.Status) AS CountOfStatus, CountOfStatus/[# of Demos] AS Percentage
FROM [Promo count] INNER JOIN DemoOrder ON [Promo count].PromoNo = DemoOrder.PromoNo
WHERE (((DemoOrder.Status)="E"))
GROUP BY [Promo count].PromoNo, [Promo count].[# of Demos];
View 1 Replies
View Related
Nov 15, 2006
Hello,
I've been trying to modify a database I downloaded from the Microsoft site
entitled
'Inventory management database.mdb'. Using Macros developed by microsoft for their example database (which I've
modified) a macro opens up a list of reports - which can be individually opened up by
clicking on them. However, when I try to open up one of my reports which
appears on the list, I get the following error message:
"An expression you entered is the wrong data type for one of the arguments
You tried to run a macro or use a method to carry out am action, but an
expression
evaluated the wrong data type."
I guessed this meant I had perhaps specified the wrong datatype in my table,
rom where I had written my list of reports, but this all seemed okay when I
checked and compared it with the working original. I had specified text (and
autonumber for the report ID number)
Can anyone point me in the right direction with this?
Many thanks.
Adam
View 1 Replies
View Related
Sep 21, 2006
Hi all,
I have a couple of subforms on one particular PC where the data is showing in some strange font, WingDings or something like that. On my PC the data shows correctly.
Please see screenshot here: http://www.confetti.ie/screenshot1.jpg
I tried changing the Font Name for all the controls in the subform to common fonts Arial, Times etc but it still shows as WingDings.
I also tried changing the default font under Edit > Options to common fonts with no change. In the Options I tried changing font options on the Datasheet and Tables/Queries tab.
Thanks
Melt
View 7 Replies
View Related
Mar 28, 2014
I am using the following control source for a text box on a report: =nz([Raw_Turb], "---")When I do, it pulls data from a different field from the data source. I am pulling the data directly from the data table. I have tried creating a query and get similar results only from a different data field!I have tried the same control source on a different report and it works correctly! Why does it not work on the current report?
View 5 Replies
View Related
Apr 30, 2015
I am writing the following query to insert data in tblpostroom from another table. Both the tables have same field names like Date1,582,1810.Now If I run the following query then in the fields 582,1810 of tblpostroom , the data gets stored 582,1810 respectively for all the records. But in actual fields 582 and 1810 store different data.
Code:
INSERT INTO tblpostroom ( Date1, 582, 1810 )
SELECT Date1, 582, 1810
FROM Sheet1;
View 5 Replies
View Related
Nov 5, 2013
I my form i have a textbox into which the user can enter a value that will serve as criteria in a query.
In the beforeupdate of this textbox there is a check about the validity of the input. If this input is wrong, a message tells the user what's wrong .
In such a case i like to cancel the event but in the same time clearing the textbox.
Canceling isn't a problem, but automatically clearing the wrong input seems not to be so evident.
How can i do that ?
View 5 Replies
View Related
Mar 19, 2008
You know the scenario where people keep working in an old version of a database, while you work on an update... leaving you with a new database, but old data?
This concerns a database, containing about 20-25 tables all linked together.
I was asked to make an update. In this update 3 new tables were included and here and there a few of the old tables got news fields, nothing crucial and a lot of forms were changed.
The current situation:
Database 1: contains new data, misses a few tables, is missing some fields, forms and queries
Database 1.1: contains old data, but some new extra tables, added fields, forms and queries
I would like to know if it is possible to import all data from 'database 1' into 'database 1.1', so that all linked information between the data is preserved, all database relations are maintained.
If I import tables from Database 1 into database 1.1, tables just get added, not replaced. If I delete or rename tables, I loose my relationships.
I would like to know what is a common and effective way to get all the new data of an old version of a database (where people keep working in while you work on an update), into the new version of the database, that currently contains old data.
View 3 Replies
View Related
Jan 31, 2006
Hi everyone,
I have a relationship between Market data table and Trade table which is One to Many. Market on the One and Trade on the Many.
However I just found out that I am unable to enter information into the Trade Form without entering information into the Market Form first.
I would like to enter info. into Trade Form/tables independent of whether I enter info. into the Market Form/table, but I still need to have the relationship of One Market data to Many Trade date.
Thanks
dfuas
View 5 Replies
View Related
May 31, 2005
Hi, I want to create an access app that will allow a user to see the number of pages printed by various printers.
These are the data that I have:
Model Type (various are available)
Printer's name (unique to one printer)
IP addr (unique to printer)
Quantity printed (such as Single Legal, Double Legal, Single Letter,etc)
This is what I have (2 tabels with 1-many relationship):
Printers
PrinterID
Model
Name
IP
ModelTypes
ModelID
Model
Now, where do I place the quanties of paper printed? Do I just leave it in Printers?
Thanks.
View 9 Replies
View Related
Nov 22, 2006
Hi All
I've worked with Access in the past but haven't touched it for 6 years now so I've forgot a lot of the things i've learnt. Bare with me while I try and explain it as clearly as possible
I'm creating a Database for work which will register all our Commerical Clients, which projects the clients are related to and which students worked on them projects. So I have 3 main Tables:
1.Commercial Clients
Client ID
Company Name
Contact
Title
2. Commercial Projects
Project ID
Nature of Project (Graphic Design, Multimedia, Media)
Project Name
Project Information
Staff Member Responsible
Start Date
End Date
3. Students/Graduates
Student ID
Student Name
Area of Expertise
Contact Details
Last Project worked on
Right here we go.....
Each commercial Client can have more then one project to their name. Each Project can have more than one student working on it and each student can be working on more then one project.
My relationships are set up like this:
Commercial Clients <One to Many> Projects
Projects <Many to Many>Students
What I have done is create forms with sub forms in to enter to extra data. I dont know if this is the right way to do it but it seems to easiest way. Please show me a way that will work better if you know it.
Anyway... the way the tables are now I can on cascade the information down through the tables.... it will not cascade upwards. I have ticked everything you can possibly tick for all relationships. So when I can enter projects in the Subform on the Commerical Clients Form and I can enter students on the subform in the Projects form. But on the Student form the projects the students at on does not show up in that subform and entering the day only tries duplicating it (which i dont want to do). If I had a form that showed the projects and the related commercial clients in the subform it wont find the commerical clients. So i.e.
It shows fields when the cascaded down like:
Commercial Clients
V
V
Projects
V
V
Students/Graduates
But it wont show if its:
Commercial Clients
^
^
Projects
^
^
Students/Graduates
Anyone have any ideas how I get get them to cascade upwards?
Thanks
Pete
View 9 Replies
View Related
Aug 15, 2006
K,
Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never).
I have a form with several tabs, linked to a table with employee information, works like a charm.
Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings.
Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab.
I definately don't want any relationships with the employee table.
Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here.
Much appreciated,
View 6 Replies
View Related
Aug 26, 2014
My problem is displaying data in many-to-many relationships.
There are three main tables: tblShops, tblOwners and tblMarketingEvents.
These are linked through two junction tables creating two many-to-many relationships:
1) Each Shop may have more than one Owner and each Owner may have more than one Shop.
2) Each Marketing Event may reach more than one Owner and each Owner may be reached by more than one Event.
Now I want to display for each Marketing Event:
1) the data of that event, (tblMarketingEvents)
2) plus the owners reached in that event, (tblOwners)
3) plus all the shops owned by the owners reached in that event. (tblShops)
Preferably, I want to display more than one record at a time without repeating any information.
Is there a way to display the data like this?
I tried a query: it gives the right data but does not display it as I need. It repeats the data from tblMarketingEvents and tblOwners for each shop.
I tried a form with subform: it can only display one record at a time and is hard to work with (printing etc)
I tried a report with grouping levels based on the query: Again, right information, wrong display. I need to group according to the records of the MarketingEvents table and not just according to a single field.
View 3 Replies
View Related
Jun 4, 2013
I have created a form with two subforms. The context of this is a litigation database. The main form is for testimony. Among other things, it includes a field for a question and an answer. Any given piece of testimony may relate to one or more documents, and any document may be referenced in one or more pieces of testimony. So have two subforms, one for documents produced by the Plaintiff, and one for documents produced by the Defendant. It worked out easier that way because I originally created indices of those documents in separate Excel tables which I then imported to Access.
So the relationship for both document tables relative to the testimony table is many to many, which is established using two junction tables.
I also have a combo box in each subform where I can autofill fields based on selection of the document number.
My problem is that while the combo-box works great, once I select a document in the subform, that selection is carried over into the next entry of the master form, but also the selection is not retained in any of the master forms. So I can browse the document listing from any entry in the master form, but I can't get the document selection in the subform to stick and create a lasting relationship to a given record in the master form.
Is my problem that it is just impossible to have a subform based on a many to many relationship, or is it in the parent child relationship of the form and subform, or is it in the properties of the subform?
View 13 Replies
View Related
Aug 5, 2013
I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.
tblLangInfo: LangID (PK), language name, language family, etc.
tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in
tblSegmentLangJoin: LangID (PK), SegmentID(PK)
I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:
tblProcesses: ProcessID (PK), Process name
tblProcessLangJoin: LangID (PK), ProcessID (PK)
Here's the fun part...
I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:
Process1_triggers
Process1_targets
Process2_triggers
...
This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.
The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.
E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.
I've attached a picture to illustrate the relationship I need, since that's likely clearer.
One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table. How to appropriately define my data tables and relationships.
View 3 Replies
View Related
Feb 19, 2014
I have a DB setup with users and their information. How can I remove a user from the DB without losing their data?
Basically I just don't want the users seeing this (deleted) user in the list on a form. So maybe "hide" is a better term than delete.
Details:
Access 2010
Table "Employees" - holds username and some other user specific info.
Table "Hours" - holds username related data(how many hours they worked on specific dates).
Form "WorkHourEntry" - The form I use to allow the users to login and enter their data(hours).
Then have a combo box that displays all the users in a list. This list/combo box is where I want to hide users from when the employee leaves the company or dept. and is no longer needed. But, we need to keep their history/data.
View 5 Replies
View Related
Jul 21, 2007
I'm attempting to normalize an enormous table with order data, but I'm running into some problems. The table currently contains many duplicates, of which also included the actual order information (yikes!), but I managed to normalize it almost all the way down. It appears that different accounts can be used on orders, and these order numbers are being recycled for some reason months down the line (don't ask my why they're reusing them for future orders because I have no idea either, they should be creating new order numbers). Of course, the Order number is the primary key in my table as it should be. I guess the same thing can occur with the sales rep. Anyway, I'm struggling to find the "best practice way" to deal with this situation. I'm almost tempted to create an intermediary "transaction table" or something like that between the main general order information (which at this point will basically be the Order Number and Customer ID only), then include a table with the account information and sales rep info, then have that link to the Order Detail with the products, quantity, order number and various dates for those order numbers. Order maybe it should be a separate, related table, but not between the general order information and the order details? Can anyone tell me if I'm on the right track for this situation? It was a total curveball that the rep and account information could be different on these orders.
Option 1:
Order (Order #, Customer) -> Transaction Information (Order #, Account Type, Sales Person) -> Order Details (dates, products, quantities, etc)
Option 2:
Order (Order #, Customer)---> Transaction Information (Order #, Account
| Type, Sales Person)
|
|-> Order Details (dates, products, quantities, etc)
View 2 Replies
View Related
May 3, 2013
I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).
I cannot seem to define a form based on these relationships to input the data.
View 14 Replies
View Related
Sep 21, 2005
Ok Guys,
Here goes...
all of a sudden now, when i open my form, its not displaying any of the previous data that i stored in it!! All the data is still in the table, and when i add data it goes into the table!! I checked properties and the record source and everything is ok there, what have i done wrong guys...HELP!!!!
View 3 Replies
View Related
Jun 9, 2006
I purchased a copy of Office 2003, on Ebay, for less than half the price it normally sells for. I created databases, which work well on my computer. However, when I copy them over to the office computer from my computer, via Flash memory stick, the program does not run. The message seems to indicate a problem with registration. When I installed Access, it asked me for a registration key, which I did provide. After that it worked-not activation process. Does this mean that you can only use the computer you created the database on to run it, or that I do not have a proper version of Access 2003? I cannot transfer, from my home computer, to the office computer. Is this Microsoft paranoia again?
Subsequent uninstalls and reinstalls of the program did not ask for the installation key.
What should happen? I would like to resolve this major problem and your help will be greatly appreciated. The program is flaky. Also, Switchboard does not work-must use Option Box.
These questions most user must know about initial installation and copying mdb files to other computers -please reply.
View 2 Replies
View Related
Oct 16, 2006
Hi,
Can anyone tell me what's wrong with this:
DoCmd.RunSQL "INSERT INTO tblPersoaneJuridice (Denumire,Localitate,CodUnicInregistrare,NumarInre gistrareRegComertului,Telefon,Fax) VALUES ('" & Forms!frmPrincipal!subfrmTipVersus!txtDenumire & "','" & Forms!frmPrincipal!subfrmTipVersus!txtLocalitate & "','" & Forms!frmPrincipal!subfrmTipVersus!txtCUI & "','" & Forms!frmPrincipal!subfrmTipVersus!txtNumarInregis trare & "','" & Forms!frmPrincipal!subfrmTipVersus!txtTelefon & "','" & Forms!frmPrincipal!subfrmTipVersus!txtFax & "')"
cui = DLookup("[CodUnicInregistrare]", "[tblPersoaneJuridice]", "[NumarInregistrareRegComertului]='" & Me!subfrmTipVersus!txtNumarInregistrare & "'")
i have a string variable denoted by cui and i need it to take value from table tblPersoaneJuridice from field CodUnicInregistrare. CodUnicInregistrare and NumarInregistrareRegComertului is text.
View 3 Replies
View Related
Jun 14, 2007
Have not coded for about 5 years and need a little refresher. Can't remember what I need to do. Thanks
Dim SelectedCompany As String
Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim sSql As String
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
SelectedCompany = Me!cboCompanyName.Text
sSql = "select Customername, AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, AddressLine6 from customers where "
sSql = sSql & "CompanyName = '" & SelectedCompany & "'"
rs.Open sSql, Cn, adOpenStatic, adLockReadOnly
this last line fails with error "No value given for one or more required parameters."
The code is executed when someone selects something in a combo box on a form in an access database.
Thanks
Pete
View 4 Replies
View Related
Oct 4, 2007
I think I'm just tired, and not seeing what to do here.
I have my main table.
MainID PK
bunch of other fields...
I also have a 'created by table'.
Createdby_ID PK
User ID
Main ID
Date_created.
I also have a user table, with a list of all the users.
User_ID PK
User_Name.
Is this the right way to setup these tables?
Only 1 person can create any paticular record.
I want it to be required tha whoever creates the record selects their name and dates it.
View 11 Replies
View Related
Dec 13, 2005
strSQL = ("SELECT onespace2(MeetingMinutes!MinutesID, '.' ) & " - " & [MinutesTitle] AS Item, MeetingMinutes.MeetingCode, Minutes.*, MeetingMinutes.MinutesCode, MeetingMinutes.MinutesID ")
strSQL = strSQL & ("FROM Minutes INNER JOIN MeetingMinutes ON Minutes.MinutesCode = MeetingMinutes.MinutesCode ")
strSQL = strSQL & "WHERE MeetingMinutes.MinutesCode= " & MinutesCode & " AND MeetingMinutes.MeetingCode= " & [Forms]![MinutesMFnew]![ComboMeetingNav] & ";"
cant get it running.
i got the same query running made in the querybuilder, but somethings change, so i need to adjus it in vba. But it doenswork, and get a type error
please advice
View 1 Replies
View Related
Jul 13, 2007
DoCmd.RunSQL "INSERT INTO tblAccessLog " _
& "(DateLoggedOut, TimeLoggedOut) VALUES " _
& "(#" & Date & "#,#" & Time & "#) WHERE " _
& "(LogID = '" & LogsID & "');"
Could anyone possibly tell me why this isn't working? Probably something stupid i've done bus still.
When i run it, I am getting the error message "Query input must contain at least one table or query" yet i've told it to use tblAccessLog so i'm just confused.
Thanks
View 8 Replies
View Related
Aug 14, 2007
StrSQL = "INSERT INTO LV (Directory_Name:,Filename:, Total_Files,Total_Records:,Total_Batches:, Total_YH) VALUES ('" & DirectoryName & "', '" & FileName & "', TotalFiles , TotalRecords , TotalBatches , TotalYH);"
Have tried loads of combinations of the " ' and ' " to get it working but just keeps giving me the damn syntax error!!! :mad:
What's wrong with it? Thanks
View 2 Replies
View Related
