hi thnks for your advice in my problem. but i dont know how to use the foreign key. i only know primary key.
pls reply if you know this x) ASAP
charli
Last Activity: Sep 10 '09, 01:56 PM
Joined: Nov 10 '08
Location:
-
charli replied to HI good day to all... i have a problem on how to connect the 2 tables in a database?in Visual BasicDo you actually need two tables here? Do students have multiple fees each? If you do need two tables you're going to want to do
Code:tbStudInfo studno studname
then join them using primary and foreign keys on 'studno', as debasisdas saidCode:tbStudfees studno studfee
-
Hmm, the mysql documentation does use language thats a bit hazy on that doesn't it.
Mysql_insert_id () looks at the current connection, so should always returns the relevant value with no concurrency issuesLeave a comment:
-
The two days I spent trying to work out how to ask this obviously didn't work, think I just confused myself more, sorry.
FishVals idea worked a treat, here is the complete SQL I ended up using to display a weekno and a count column (mondays being the table week numbers):
...Code:SELECT COUNT(weeknos.weekraised) as count, mondays.weekno FROM weeknos RIGHT JOIN mondays ON weeknos.weeknoraised <= mondays.weeknoLeave a comment:
-
With Query1 being the structure it is, I can't work out how to put a group by on it. It needs a specific week number to work on, rather than creating results that can be aggregated.
Table:
status
dateraised
dateupdated
Query1 takes a weeknumber, and counts the number of records that were raised before that week number, but updated (ie: completed) after that week number (which is then the number...Leave a comment:
-
well currently people use the parameter query, every week they type the weeknumber in and append it to an excel spreadsheet. I was hoping to cut some stuff out by making a query/report that listed the weeknos and ct (which is a number of outstanding orders). Query1 is the query we already use to count oustanding orders, but I'm unsure how to incorporate it so that I can get an ongoing list of weeknos and the relevant [ct]Leave a comment:
-
SQL joins and parameter queries
I have a parameter query, the parameter it asks for being weekno:
Query 1:
This works correctly, but I would like a new query result that lists all the weeknos and the result of Query1, so the weekno would supply the parameter to Query1.Code:SELECT count(*) as ct, weekno from weeknos where weeknoraised <= [weekno] and weeknoupdated > [weekno] and status='completed'
I... -
Fantastic, thank you sphinney!Leave a comment:
-
Getting sql string from a query
Is it possible to programmaticall y get the sql string of a query object? -
How I ended up doing this, incase anyone has the same problem:
Query 1 (called top10): SELECT TOP 10 streamid FROM table WHERE streamid=[Reports]![Confirm]![streamid] ORDER by [date] DESC;
-- returns the top 10 most recent records for an id
Query 2 (called Avgtop10): SELECT AVG(top10) FROM top10 WHERE streamid = [Reports]![Confirm]![streamid];
-- returns the average of the top 10 most recent records for...Leave a comment:
-
Thank you for your help! I think I give up now, and I'll just settle on the overall average, rather than the average fo the last X records, its a bit too much for my poor brain to handle!
Thanks,
CharliLeave a comment:
-
Hmm, I get a 'syntax error in FROM clause', and it highlights the later one but even if you attempt putting the innermost query in, you get the same error
It doesn't like the placement of 'As' or using keywords Inner and Outer, but even once those are altered (which could be breaking its intended use horribly, sorry):
...Code:SELECT [streamid], (SELECT Avg([Weight]) as avg FROM (SELECT TOP 10 [Weight] as inn
Leave a comment:
-
Query for average of the last 10 records
I have a table of Waste IDs, with a relationship with a second table of Waste Weights:
WasteIDs
WasteWeights:Code:id name 1. Paper 2. Cardboard 3. Cans
I want to look up the last 10 wasteweights for an id, and get an average.Code:Id date kg 1. 1/1/09 12 1. 1/2/09 24 2. 1/1/09 2
So I can do:
...Code:SELECT avg(weight) FROM (SELECT TOP 10 weight FROM -
Both answers work beautifully! I have no real problem with using DAO, but thank for explaining why the original version didn't work!
CharliLeave a comment:
-
ADOX Error 3265- "Item cannot be found..."
Error 3265, "Item cannot be found in the collection corresponding to the requested name or ordinal"
code programatically opens a query using ADOX and changed the sql
...Code:Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Procedures("qryLostCostCodes").Command 'opens existing query to edit -
For data entry into the same database?
Use bound forms (such as created using the Create Form Wizard) that makes forms directly based upon your tables or queries
Or make your own unbound forms, and use DAO or ADO to put the data in the appropriate tables
You'll have to be a bit more specific to get a more specific answer.Leave a comment:
-
Great plan! And it works too. Sometimes the simplest answers work the best!
Thank You!Leave a comment:
-
Data type Mismatch and delimiting different data types
SQL:
Gives 'Data Type Mismatch' because the 43.38 is delimited as a string, when it is infact a number.Code:SELECT COUNT(month) as c FROM pool WHERE [reg] = "GP06NHL" AND [litres] = "43.38" AND [fuel] = "NORMAL UNLEADED" AND [month] = #01/04/2009#
But, this sql is automatically generated (the columns names and data in the WHERE clause comes from another... -
Worked the DAO->ADO thing out, here is the code to make a querydef in ADO
...Code:Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command cat.ActiveConnection = CurrentProject.Connection On Error Resume Next cat.Views.Delete "qryFilter" 'deletes the query if it already exists, ignores errors if it doesn't On Error GoTo 0 cmd.CommandText = sql 'this should be your sql SELECT
Leave a comment:
-
Easy to tell isn't it- I can work recordsets to the web and to Excel, just can't work out Access wanting to do things for me!
I have a completely unbound form, it doesn't display anything from the database- you just make selections as to how you would like the data (it basically builds up the WHERE clause of SQL, example: grouped by week/month/year)
I've managed to get a report to do the SQL-building (based on the open...Leave a comment:
No activity results to display
Leave a comment: