Complex Update Issue
May 11, 2007
I could do with a bit of help on what has become a complex update issue to me.
I have a junction table to allow me to have a many to many relationship called Nominal_Junction. This relates a table called Principle_Nominal_Table to a table called Information_Scores via Integer fields called NominalID and InformationID.
The Information_Scores tables has a Integer field that is the total score for that record.
As you would expect from a junction table, a nominal can have many information records associated with them, and a Information record can have many nominals associated with them also.
I need to know the total score for each nominals summed information reports.
ie nominal 31 is associated with records 2, 4 and 5, the scores of which are 7, 6 and 3 hence the nominals total score for nominal 31 would be 16!
I thought the simplistic approach would be to re-calculate all the nominal scores everytime a record is saved. I created a query to calculate the scores against the nominalID in design view. This worked. I then tried to use this in a SQL update statement. See below. I cannot for the life of me get it to work and its gone way beyond me. Any help would gretaly be appreciated as this is the last module I need to get working.
See code:
strSQL = "UPDATE Principle_Nominal_Table " & _
"SET Overall_Nominal_Score = (SELECT Sum(Information_Scores.Overall_Score), As Overall_Nominal_Score, NominalID " & _
"FROM Information_Scores INNER JOIN Nominal_Junction ON Information_Scores.InformationID=Nominal_Junction. InformationID) " & _
"WHERE ((Principle_Nominal_Table.NominalID = Nominal_Junction.NominalID ));"
As stated, any help would be appreciated as my mind is now scambled!:eek:
View Replies
ADVERTISEMENT
Oct 30, 2005
I need to import information from an XML file that includes a wide array or codes. Each code stipulates which amount is entered into a particular field.
The importation and conversion is not a problem this has been achieved successfully, however the problem exist in the update procedure. Due to the extensive amount of different codes (est. 30) an IIF statement becomes to large and complex to evaluate.
The following statement though being very incorrect it does show an example of what is being attempted.
IIf([ClaimUpdate]![claimtype]="com1" Or "com2",[tblClaims]![CommencementCIAmount],"") Or IIf([ClaimUpdate]![claimtype]="RCM*",[tblClaims]![ReCommencementCIAmount],"") Or IIf([ClaimUpdate]![claimtype]="SCH1",[tblClaims]![SchoolBasedCommAmount],"")
Basically if table [claimupdate]![claimtype] = "com1" then [tblClaims]![Related Field] = amount else leave blank.
Question is there a better way to construct the criteria or should a module or procedure be used instead to evaluate? Or is there a way to construct a nested IIF statement to evaluate a large complex criteria?
Any suggestions greatfully recieved...
If it looks like a problem, acts like a problem it must be a problem!
View 1 Replies
View Related
May 11, 2007
I could do with a bit of help on what has become a complex update issue to me.
I have a junction table to allow me to have a many to many relationship called Nominal_Junction. This relates a table called Principle_Nominal_Table to a table called Information_Scores via Integer fields called NominalID and InformationID.
The Information_Scores tables has a Integer field that is the total score for that record.
As you would expect from a junction table, a nominal can have many information records associated with them, and a Information record can have many nominals associated with them also.
I need to know the total score for each nominals summed information reports.
ie nominal 31 is associated with records 2, 4 and 5, the scores of which are 7, 6 and 3 hence the nominals total score for nominal 31 would be 16!
I thought the simplistic approach would be to re-calculate all the nominal scores everytime a record is saved. I created a query to calculate the scores against the nominalID in design view. This worked. I then tried to use this in a SQL update statement. See below. I cannot for the life of me get it to work and its gone way beyond me. Any help would gretaly be appreciated as this is the last module I need to get working.
See code:
strSQL = "UPDATE Principle_Nominal_Table " & _
"SET Overall_Nominal_Score = (SELECT Sum(Information_Scores.Overall_Score), As Overall_Nominal_Score, NominalID " & _
"FROM Information_Scores INNER JOIN Nominal_Junction ON Information_Scores.InformationID=Nominal_Junction. InformationID) " & _
"WHERE ((Principle_Nominal_Table.NominalID = Nominal_Junction.NominalID ));"
As stated, any help would be appreciated as my mind is now scambled!:eek:
View 1 Replies
View Related
Sep 4, 2005
I am accessing an oracle database that has several thousand records in it. I am quering for specific requirements, but would like to save my query results in a local access database for faster searching capabilities. Is there a way for me to set up a query that will go out to my oracle table files, select the records that pertain to my search criteria, and add records to locally stored tables without duplicating itself each time that I run the query? I would appreciate any assistance in this matter. Thanks for your help!
View 1 Replies
View Related
Jul 22, 2005
Hello all,
I am trying to make a simple inventory control system. This system will only record input of products and output of products. Explanation: This is a catering service company. I buy tomatoes, rice, oinions, Oils, meat, ect. When we buy this products they usaly come in different packages. The rice can come in 100 pound bag, 50 pound bag or just by the pound. The onions can be bought by the bucket, half bucket, dozen, half dozen ect. The oil can be bought by 100 gallon barril, half barril, Gallon, half gallon, pint ect. The meat by the pound.
I am able to make the a simple invoice and PO application, the only problem is that I want to be able to select what type of packaging the product has and the invoice or PO subforms.
I started with a simple product table, I than created a packaging table and a package detail table. This gave me the chance to create a subform on the product form where I can choose what type of packaging this product uses and also tell it how many units the package has.
So far so good. Now I created a Chef table "this is my Chef to whom I give the product to for cooking" I than created an authorization slip table "this is like an invoice table" and ofcourse a authorization detail table "this is where I pick the products that I give the cook" once I print this out the chef will be able to go to the warehouse and retreive the product.
What I want to do in the detail subform is to be able to pick the product and the type of packaging that I am giving them from a dropdown box then it will automaticaly show only the packaging I assinged to that product and give me the units . This way if I pick the type of packaginh name 100 pound it will put 100 units in the quantity field automaticaly.
So basicaly is an invoice form with its detail but on the detail I am able to pick the product and the packaging that I am giving the chef.
Can you guys guide me on the right direction on how to do thsi?
Thansk
DaniBoy
View 1 Replies
View Related
May 17, 2007
Hi,
I was wondering if someone can give me a possible solution to my join below:
select tableA.name
From tableA, tableB
where tableA.id IN (Select id from table c where .......) <--I would like tableB.prodId to make a join with the tableA.id's that are in this select sub query
Thanks
View 1 Replies
View Related
Aug 4, 2007
Hello ,
I had incountered with a huge problem in my project and I need your help guys.
I have a huge table contains alot of data about many people- I wanted the data will be checked and sent to a query.
Here is an example for a little project beacuse I couldn't have the big one.
(This example works fine)
My big project is pretty the same but after I try to get the query out I get an error that the query is too complex....(It's really too big when you have 20texts to be checked with 9000 fields)
If you check the query fields you may see how the OR is getting separted and because every text has OR statement everything is hanged by everything.
Someone told me that I can't use query options and mannge it and I should use VB SELECT option - but I can't make it work too.
So I can't show you the real example at all beacsue I can't have it to my computer and It will take alot of time to have 9000 different cells ;
SO if someone knows what I am talking about and ever encoutered it , I would be really thankful !
(Also - You may see some problems with the OR ("") but I mannged it to work with the BIG project so it doesn't matter)
I don't want you to focus the conditions but just the problem itself ...." the query is too complex"...
View 6 Replies
View Related
May 25, 2005
Hi,
This one is a pain in the but. I tried everything, but why would it just won't work. It always says the query is to complex. Have included the database, anyone a solution?
Thanks
View 2 Replies
View Related
Jul 27, 2005
If the user inputs two words (or names in this case) such as "Bloggs Smith", how could I get it to return records with Bloggs & Smith in whatever order they were input?
Example:
Author: Smith, Medin & Rose
User searches: Smith Rose ..........and it returns a hit for Smith, Medin and Rose.
Maybe the Author field would be better not as a text but as a seperate table with a many to many relationship...can someone suggest the best option?
Thanks
View 1 Replies
View Related
Sep 16, 2005
I need to perform a query on a database (not designed by me) that is not normalized. In fact it is only one table with numerous fields (many of them Date/Time). I need to query the table based on the date fields only.
Basically the table is used to track when specific functions are completed to determine the time required to perform the individual tasks (accuracy to one day is acceptable) and find the ones which are falling behind. Since several individuals are required to complete each project each step has an average value (based on historical data.)
The following is a short narrative:
1. Step 1 is scheduled for 1/5/05 and the task is not started untill 1/7/05. This is not acceptable. There is a 1 day window. I need to flag this record (via report) if the start date exceeds the schedule date by more than 1 day. If a start date has been entered this record does not need flagged (regardless of the alloted time).
2. Step 2 is based on the amount of time alloted to complete the task once started. If the task takes more than 2 days the project needs to be included in the query for the report. As above, if the date is entered the record is not needed since there is no need to try to determine the when the project will start.
3. Step 3 measures the number of days from the project completion untill the product is sent to the central office. If the time exceeds 2 days the record needs to be included in the dataset. Again, once a date is entered in the received field there is no need to include the record.
There are a couple more steps but they follow the same criteria as the first three.
I have not been able to figure out how to get these requirements entered into the query design view.
View 3 Replies
View Related
Sep 19, 2005
This is the table structure that I use:
CAR TABLE
==========
Car_Id
Car_Tag_Number
CLIENT TABLE
============
Client_Id
Client_Name
CLIENT-CAR TABLE
=================
Car_Client_Id
Car_Id
Client _Id
ORDER TABLE
============
Order_Id
Car_Client_Id
Order_Date
Payment_Date
Payment_Amount
ORDER-DETAILS TABLE
=====================
Order_Detail_Line_Number
Order_Id
Item_Id
Order_Detail_Price
Order_Detail_Qty
The query I'm trying to get is: Who owes me money for the service and How much.
Each car had several treatments in the past and some have missed a payment or I didn't notice and just issued a new order.
I need a query that runs through the entire database, does a summary of all the amount owed to me per Car, and then deducts the total payments made per Car. If the balance is > than Zero,. Show me that car and the bottom line amount.
Please let me know if you can help with this.
-Alon
alon@wsco.com
View 1 Replies
View Related
Dec 14, 2005
Hi guys, long time surfer, first time poster here :)
We use a prehistoric Process Plant design software package, and it stores all its information in Oracle 8i.
A report I need to pull takes data from a stack of tables, each with a unique number.
PDTABLE_113 contains a list of models, each model having a unique PARTITION_NO.
Every PARTITION_NO entry has a matching table called PDTABLE_21_XXXX where XXXX is the PARTITION_NO of the model. Each PDTABLE_21_XXXX has a row for every piece of equipment in the relevant model.
PDTABLE_21, for argument sake, has the columns EQUIPMENT_ID, EQUIPMENT_DESCRIPTION, and EQUIPMENT_STATUS.
Is there some way to query the database so I get something that looks kinda like this:
MODEL_NO=====PARTITION_NO=====EQUIPMENT_ID===etc..
A1A1M01======1516=============XYZ-110-A=====etc..
A1A1M01======1516=============XYZ-111-A=====etc..
A1A2M01======1517=============ABC-122-A=====etc..
A1A2M01======1517=============ABC-123-A=====etc..
View 3 Replies
View Related
Jan 31, 2006
Hello all,
I am experiencing this error:
"This expression is typed incorrectly or it is too complex to be evaluated….For example a numeric expression my contain too many complicated elements."
I think the error is as a result of this expression in my query, I have 9 fields with the expression below with different field names:
Like Forms!QBF_Form!Sales & "*" Or Forms!QBF_Form!Sales Is Null
The problem is when I close the query and open it, the expression multiplies to numerous rows and also creates additional fields for the last part of the expression. Is there an expression that I could use that would work okay.
I tried using this expression:
Like IIF (Forms!QBF_Form!Sales)="", "*", "*" & Forms!QBF_Form!Sales & "*"
It was working, but is no longer working..not sure why.
Do you have a similar expression that will achieve the same result but create additional criteria.
Very grateful!
View 4 Replies
View Related
Aug 31, 2006
Hello, this is kinda complicated to explain so Ill try to break it down.
I have a table with the following sample data
SerialNumber DateReceived Measured Level
0000-0024 25/08/2006 11:31 84
0000-0024 25/08/2006 12:59 84
0000-0024 25/08/2006 15:05 84
0000-0021 25/08/2006 10:08 32
0000-0021 25/08/2006 17:19 32
0000-0024 24/08/2006 09:45 88
0000-0024 24/08/2006 16:06 88
0000-0021 24/08/2006 13:09 36
etc
this shows that I can have multiple entries in a day for a particular serial number.
I need to select ONLY ONE serial number and corresponding data for each day (or week).
I tried to format the date to remove the time and then select the distinct date (so 1 record a day for each serial was displayed), this worked.
BUT
I could not link it successfully to other tables because I had to format the corresponding match date to avoid a type mismatch thus invalidating what i was doing by selecting the distinct record.
Here is the original query i was using that selected a range of dates (which included multiple dates in a single day with a single serial).
SELECT TBLRemoteUnitInfo.TankSize, TBLRemoteUnitInfo.TankName, * FROM TBLRemoteUnitRequests, TBLRemoteUnitInfo WHERE (TBLRemoteUnitRequests.RemoteFeildUnitID = TBLRemoteUnitInfo.RemoteFeildUnitID) AND (TBLRemoteUnitRequests.SerialNumber=:SerialNumber) AND (TBLRemoteUnitRequests.DateReceived BETWEEN :Datestart AND :Dateend)
ORDER BY TBLRemoteUnitRequests.UnitRequestID';
how can i take a sample of the range of dates (ie 1 a day/week or month), i assume i need to create a filtered table via query first then query that table.
Its killing me!
if i am being unclear please let me know and ill try to clarify
Dan
View 3 Replies
View Related
Feb 6, 2007
Hi
I have come to a dead end with my query. Any help would be really appreciated.
I have a query which i have written:
SELECT TblBurnleyWwTw.MetricID
FROM TblBurnleyWwTw
WHERE (((TblBurnleyWwTw.[Data Source])="OMS")) OR (((TblBurnleyWwTw.TAGFunction)="CHP"))
GROUP BY TblBurnleyWwTw.MetricID, TblBurnleyWwTw.[Metric Required], TblBurnleyWwTw.CALCULATIONS, TblBurnleyWwTw.PIPointSource, TblBurnleyWwTw.PILoc1, TblBurnleyWwTw.[High Level KPI]
HAVING (((TblBurnleyWwTw.MetricID) Like "130*") AND ((TblBurnleyWwTw.[Metric Required])="-1") AND ((TblBurnleyWwTw.PIPointSource)<>"L"));
This retreives all codes relating to what i want. The problem is, I need this data to try and match within another column of calculations E.g. Data retreived from query I have may be 13001, 13002, 13003. What I would like to do with this data is to lookup in a calculations column if any of the above data matches to bring back the metric ID which is realted to it?
Hope i have stated this clearly enough for anyone to understand???
As i say any help or recommednations for a solution would be really appreciated
Andrew
View 6 Replies
View Related
Mar 29, 2007
SELECT [UK Table].[Business Name], [UK Table].[Business Type], [UK Table].Address, [UK Table].City, [UK Table].Country, [UK Table].[Telephone Number], [UK Table].[Website Address], [UK Table].[Email Address]
FROM [UK Table]
WHERE ((([UK Table].[Business Name]) Like [Forms]![frmNz]![txtBusinessName] & '*' Or [Forms]![frmNz]![txtBusinessName] Is Null) AND (([UK Table].[Business Type]) Like [Forms]![frmNz]![txtBusinessType] & '*' Or [Forms]![frmNz]![txtBusinessType] Is Null) AND (([UK Table].Address) Like [Forms]![frmNz]![txtAddress] & '*' Or [Forms]![frmNz]![txtAddress] Is Null) AND (([UK Table].City) Like [Forms]![frmNz]![txtCity] & '*' Or [Forms]![frmNz]![txtCity] Is Null) AND (([UK Table].Country) Like [Forms]![frmNz]![txtCountry] & '*' Or [Forms]![frmNz]![txtCountry] Is Null) AND (([UK Table].[Telephone Number]) Like [Forms]![frmNz]![txtTelephoneNumber] & '*' Or [Forms]![frmNz]![txtTelephoneNumber] Is Null) AND (([UK Table].[Website Address]) Like [Forms]![frmNz]![txtWebsiteAddress] & '*' Or [Forms]![frmNz]![txtWebsiteAddress] Is Null) AND (([UK Table].[Email Address]) Like [Forms]![frmNz]![txtEmailAddress] & '*' Or [Forms]![frmNz]![txtEmailAddress] Is Null));
What is wrong? I have attached the database. The form frmNz is what I want to work. I should be able to enter company information into at least one field, allowing me to retrieve the specific company data from the UK Table in the subform.
Gurdip.
View 12 Replies
View Related
Jun 28, 2007
I have three tables called, Clients basic details, Episode and Modality. There are some 56 fields in these tables. Once a month I run a query on these tables. The data is then uploaded to another site via the internet.
I have now been asked to collect data into another table called TOPS which has some 23 new fields in it.
The problem I have is, they want none of the TOPS data to be on the same line as the Modality Data. For instance, there are 79 fields in the query which must conform to certain parameters before the data can successfully uploaded, the query must show lines of data for all the 79 fields, but if any line has TOPS information the line cannot have Modality information in it and vice versa. I think some of the data that would be collected will be duplicate, i.e. from the episode and clients basic details tables.
The query must show all 79 field headings. Any line of Data in the query result that has data from the Modality table and data from the TOPS table can only show the returned data from one of these tables, the values from the other table must be left blank and vice versa.
For Instance say From the clients details table we show the clients name, from the episode table we show the number of children he has, from the Modality table we show he has structured intervention, we then must show blank records from the TOPS table.
Then on another line the query must pick up the data from the TOPS table, episode table and clients basic details table and leave the Modality table fields blank.
A lot of the info from the Clients and episode table will be duplicated but on the different lines.
Any Help would be greatly appreciated
Barry
View 2 Replies
View Related
Jul 24, 2007
I am trying to create ranges in data in order to create a graph. I have the following IIf statement that access says is too complex and also Im getting an error saying that it is too long to edit so does anyone have any ideas how to do this with out these problems. I need to get to $25000 $30000 in increments of $1000.
IIf([Actual$/Mile]<1000,"$0 to $1000",IIf([Actual$/Mile]>=1000 AND [Actual$/Mile]<2000,"$1000 to $2000",IIf([Actual$/Mile]>=2000 AND [Actual$/Mile]<3000,"$2000 to $3000",IIf([Actual$/Mile]>=3000 AND [Actual$/Mile]<4000,"$3000 to $4000",IIf([Actual$/Mile]>=4000 AND [Actual$/Mile]<5000,"$4000 to $5000",IIf([Actual$/Mile]>=5000 AND [Actual$/Mile]<6000,"$5000 to $6000",IIf([Actual$/Mile]>=6000 AND [Actual$/Mile]<7000,"$6000 to $7000",IIf([Actual$/Mile]>=7000 AND [Actual$/Mile]<8000,"$7000 to $8000",IIf([Actual$/Mile]>=8000 AND [Actual$/Mile]<9000,"$8000 to $9000",IIf([Actual$/Mile]>=9000 AND [Actual$/Mile]<10000,"$9000 to $10000",IIf([Actual$/Mile]>=11000 AND [Actual$/Mile]<12000,"$11000 to $12000",IIf([Actual$/Mile]>=12000 AND [Actual$/Mile]<13000,"$12000 to $13000",IIf([Actual$/Mile]>=12000 AND [Actual$/Mile]<13000,"$12000 to $13000",IIf([Actual$/Mile]>=13000 AND [Actual$/Mile]<14000,"$13000 to $14000",0
))))))))))))))
View 14 Replies
View Related
Oct 10, 2007
Hello everyone!
i hope someone can help me with either:
1. writing a query to get the results needed (will explain below)
2. write VBA to be used in query to get the results needed
3. or change table(s) to be able to obtain the data in all areas to get the results needed.
The result needed:
How can I get the result of ONLY when there's an exact match of the combinations and not all combinations of the matches?
I have a GuitarOptionDetails and it's linked (LEFT OUT JOIN) to a ProgramCodes.
so the basic result here is displaying correctly. it's showing everything in my GuitarOptionDetails and only where there's matches from the ProgramCodes. Linked on Guitar and Option as these fields are in both sides. The ProgramCodes has the "Code" for the guitar and guitar / option combinations.
Issue, and why I'm seeking help ... i need to somehow change that so it will only show the set of results where the combinations matches.
example
here's the "raw" data from ProgramCodes table
GuitarOptionCodeComboID
AE185185RR1
AE185186RHT1
AE185187RT1
AE18538185RR2
AE18538186RHT2
AE18538187RT382
AE185BB185RR3
AE185BB186RHT3
AE185BB188RT-B3
AE18538185RR4
AE18538186RHT4
AE18538188RT38B4
AE185BB185RR4
AE185BB186RHT4
AE185BB188RT38B4
GuitarOptionDetails is showing the Codes for ComboID's 2, 3, and 4 because an invoice for AE185 has BOTH 38 and BB. Again, the basic LEFT OUTER JOIN query is showing all the Codes for AE185 where there's 38 or BB.
But instead, i need the result to show only the ComboID 4 Codes and not to repeat the same Codes for the ComboID 4 Codes.
I truly hope I made sense and there's a solution for this. I have no idea how to look it up to see if there's any previous posting for this or something like this, etc ...
Thank you in advance!
View 14 Replies
View Related
Jan 5, 2007
I'm trying to design a system for an electronics shop. I have two tables, one called Products and another called Suppliers.
The Products table has the following fields-
- 1) Product ID
2) Supplier ID
3) ProductName
4) Information
5) Price
The Suppliers table has the following fields-
- 1) Supplier ID
2) Company
3) Address 1
4) Address 2
5) Address 3
6) Town
7) County
8) Post Code
9) Country
10) PhoneNumber
11) FaxNumber
Basically I want to create a form to show the supplier name and ID and then implement a sub- forum which shows all the product details for that supplier. How do I do this? Do I have to use a query?
Thanks
View 2 Replies
View Related
Aug 30, 2007
Hi,
I am trying to build a database to keep records of devices that I have.
The basic ERD is as so:
hXXp://img50.imageshack.us/img50/3082/erdkt3.gif
I want to create a form where the user selects the building, then is given a list of rooms in that building, the is given a list of Racks in that room.
I have tried added fields Bldg and Room in the device table. The bldg drop down would
SELECT Bldg_ID, Bldg_Name
FROM bldg;
Then in Room i did a:
SELECT DISTINCT [Room].[Room_ID], [Room].[Room_Name] FROM Room, Device
WHERE Device.Bldg_ID=Room.Bldg_ID;
Then in Rack I did:
SELECT DISTINCT [Rack].[Rack_ID], [Rack].[Rack#]
FROM Rack, device
WHERE Rack.Room_ID=Device.Room_ID;
This work except for one flaw. there where statement limits what room/rack for all bldg's or rooms in the table not just that current row.
I then started to think I need to do this in a form. But i run into the same problems. I hope this makes sense.
Send me a PM and I can send you a copy of the file.
Thanks,
Mitchell
View 5 Replies
View Related
Jan 18, 2007
Hi. Hopefully I explain this well.
Each calendar quarter we get a table which has records of client wage earnings.
Each record has simply the ssn and a currency field for each of 13 calendar quarters, named for the appropriate quarter. (i.e., 1/2005, 2/2005... 1/2006, 2/2006... ) - Each table has 12 overlapping quarters' worth of data. (Constantly adding a quarter and dropping the earliest quarter.)
Unfortunately, wage data sometimes changes for various reasons, so that one table's 1/2006 wage for Joe Smith might be different in the next table that we get.
An administrator needs to compare, at one visual shot/report, all the tables' values for a given quarter for a given ssn.
i.e., For Joe Smith's ssn, all 12 tables' worth of wages for the 1/2006.
How best to do this?
Russ
View 2 Replies
View Related
May 23, 2007
The control source for the Activity Summary report in my DB is the primary data table. The report contains about 12 fields, each with a statement like this one as its control source: =Sum(Abs(Year([RecDate])=Year(Date()) And ([NotifType]=9)))
Up until yesterday afternoon the report was working just fine. But suddenly when I run the report I'm getting this error:
"The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I have no idea why this problem suddenly popped up when the report had been running just fine for weeks.
Here's what I've done so far to try and fix it:
1. Copied the report and gave it a new name, thinking if the report were somehow corrupt creating a new one might fix it. No such luck.
2. Tried running the report after removing each control source statement one-by-one, but the error didn't disappear until all the statements had been cleared.
Anyone have any ideas? I'm willing to post the DB if that will help.
View 2 Replies
View Related
Jun 14, 2007
Hi All,
If possible I would like some help on the design of a database. Having gone through the process of determining what the DB must be capable of, I've come across a complex issue, that I cannot determine how to resolve.
For each Group of Tests requested (1 or many) there is a series of codes that has to be recorded, each has a description and a cost which is date dependant.
So far so good. But, there a three classes of test code, Test1, Test2, Test3 - each code in the subgroups are unique.
In each group there must be at least one code for each class, however there could be multiple Test1 codes, to one Test2 code, to multiple Test3 codes. Conversley there could be 1 Test1 code, multiple test2 codes, and multiple Test3 codes. There is no distinct relationship either between the various codes...
I need to retain the overall grouping.
In case this was as clear as mud, I could have
TestRequest001
TestA..........Test1..........TestF5
..................Test3
..................Test15
TestRequest002
TestA..........Test2..........TestF1
TestB............................TestF5
TestC
TestD
Any help on how to set up the tables, and to produce a "neat" looking form would be greatfully received. Oh and I need to record the results from each of the last class of tests....
Thanks in advance
View 3 Replies
View Related
Jun 27, 2005
I am working on a complex database to store Business information as well as specific into about that Business... here is the tricky part...
The individual companies are sorted by Business type (i.e. Computer Software Companies, Child Care Providers, Commercial Building Contractors, etc.) and each Business type needs to hold a simi-customized set of information (i.e. Commercial Building Contractors needs Total AZ Billings for Commercial Bldg., $ Awarded for Completed Contracts, % of Work Subcontracted, No. of Local Employees, and Areas of Specialty). I will refer to each type of information as a Category. Each Business Type has between 4 and 7 Categories. Within a Business Type there are between 10 and 30 companies listed. There will be just over 200 Business Types listed within my database structure, as well as around 100 unique types of Categories.
Primarily, I need to be able to query and format each Business Type, to include the Companies information as well as each Category that is associated with the Business in a flat spreadsheet style layout.
Now, I intend to create a unique report (if needed) for each Business Type as needed to customize the look (i.e. column widths and any other special formatting needed) of each Business Type,
Here is where I stand currently with this project…
I have 4 tables,
1. Business (it includes basic contact info that is common to all business)
2. Category (is basically a list of all possible categories)
3. BusinessCategory (a Many-to-Many relationship container that links the BusinessID to the CategoryID and includes the corresponding Value)
4. BusinessType (a list of business types that a business can belong to)
This structure seems to work in that it can handle the customization of categories for each Business within a BusinessType. The problem I am running into with this structure is creating a query to handle the data and create a “flat” table for a BusinessType with each Business and it’s categories.
It may be that I can use this structure to do what I need done, however I think that there must be a better way to structure my data to work the way I need it to work. I really appreciate your input on this!
View 2 Replies
View Related
Aug 4, 2006
Hi All,
i have a slight problem i have a access table which has the following fields:
Name
Photocard ID
Purchase Date
Photo
Cashsaver Zone
Valid From
Period of Validation
Expiry
the period of validation field has a lookup to another table with the folwing values: 28 Days, 3 Months, 6 Months and Annual.
the valid from date is entered manually.
basically (maybe not uite the right word!) i need the the expiry field to automatically insert the correct expiry date by looking in the valid from date and adding the correct amount of time onto it according to what is selected in the period of validation field.
example:
Valid from date is 01/01/2006
Period of validation is 6 Months
Expiry date should be 01/06/2006 (01/01/2006 + 6 Months)
i came up with the following formula although i know its not really correct and some of the words arent functions at all just to show what is should be based on:
WHERE Period of validation <= DataValue ("28 Days") then DateAdd("d",28, Period of validation)
WHERE Period of validation <= DataValue ("3 Months") then DateAdd("m",3, Period of validation)
WHERE Period of validation <= DataValue ("6 Months") then DateAdd("m",6, Period of validation)
WHERE Period of validation <= DataValue ("Annual") then DateAdd("m",12, Period of validation)
also i am unsure into what box to type this into?
i would greatly apperciate some help
Spindlemania
View 6 Replies
View Related
