SQLite prob...

[Jonas DK]

I want to take data from one row of a table and insert it with new data in another table.

I have 2 Tables

Table1 and Table2

In Table1 I have columns like Name, Address, postcode, city...

I then want to store some data like a riminder in Table2 by
having a function that reads the Name column from Table 1
and then in Table2 inserts then name in the Name column and the remindertext in the reminder column.

I cant get my head around this one.... any help?

Here comes the tricky part...

In the First Table I have a category column and I want to only add the reminder to a certain category that I select from a comboBox.

I have a function that fills the combobox with the available category names.

But a function to add rhe reminder to the selected category and then saving all this to the SQLite tabel2... That is my problem

If anyone have an Idea to this one Id be much pleased...

cheers,
Jonas:huh

[bule]

You have a basic master-detail table connection here.

master:
id
name
address
city
telephone
...


detail:
id
master_id
reminder_text
time
date
...


You should save master's id in the detail's master_id field when creating reminder. This ways each master can have multiple reminders. I hope this helps a bit.

[Jonas DK]

You have a basic master-detail table connection here.

master:
id
name
address
city
telephone
...


detail:
id
master_id
reminder_text
time
date
...


You should save master's id in the detail's master_id field when creating reminder. This ways each master can have multiple reminders. I hope this helps a bit.

That is what I thourght.

The database is for a danish organisation that gets goverment funds based on the number of paying members.

So the first table I have is called tMembers and contains ID, CPR, Firstname, Lastname, Address, postalcode, Category

I can't use the ID to identify members so therefore I used the CPR (Central Personal Regristration number) to link different info from multible tables together. So when you add a new member there CPR number is also created in all other tables of the database I have 7 tables that all holds defferent info on the given member. The reason for splitting the info into multible tables is that in the first table tMembers you only have the basic info to reduce load. and then I use the CPR field from that table to Identify the info in the rest of the tables depending on the task at hand so that eatch task (or page) have its own table.

I now want to make a table to regristre when it is time for members fee.
I have a page where you can type the date when the fee is due and when you click a button on this page I then want it to get all the activ members from tMembers (only there CPR) and add that to the payment table with the due date so that when call the member Info the payment shows on screen.

Then when a member has payed there is a button to regristre payment an a true/false is set in a nother column of the payment table.

I have the structure and I have the CPR in the payment table, but I need to get info from another table firstly from the tData table that holds info (true/false) if the member is still a member and if so add the payment date to the coresponding CPR in the payment table.

It would be so much easier if I could just delete records of people who are not members any more, but since the organisation is goverment funded they must keep the members details for 10 years before deleting...(stupid rules..)


I need to create a function that
looks in tData to the colunm Memberepire for a true or false.
if its false I need to get the CPR and do a check in the tMembers table to see the category and the if the category is the same as selected in the combobox then take the CPR of those members and update the tPayment table with CPR and the text from an input field.

I've been at this all night and Im not remotly closer to have any idea on how to do this. I am not that strong in SQLite All I know is that I am now on my 3rd pot of coffee and my 2nd pack of cigerets and getting a head ache.

I'd be much pleased if any one has a sugestion for such a function.

[bule]

You should first draw your tables and their connectors down on a paper to reduce confusion.

Avoid this true/false field regarding the payments. Instead, you could have a table payments that would permanently hold all the payments ever made by members. It would hold the payment ID, member's CPR, a time/date of the payment and time/date until that payment is valid.

With true false field that is related to the member being active or not, you would have a list of members to check for payments... you would then iteratively search this table (based on the number of members to check) for the date/time payment is valid (example date format in a number like fassion 200603160800 - 3rd March 2006, 08.00h) that is bigger than the current time (use WHERE clause to filter the rows for current member being processed and to evaluate time). If no rows returned, member has to pay, otherwise he doesn't because it has at least one payment that is still effective.

[Roboblue]

Maybe I don't understand enough about what you want to help you much.
But as far as the non paying member, I would set up a new table that anyone who's lost their membership gets moved that table. That way, they won't be part of any active query. But when you need a report on the members, just include that table in the report query. Also, if they ever rejoin again with the same info, just move them back to the active tables.

[bule]

Well that is not quite the way things are usually done, AFAIK.
The data should not be moved around just like that...

Example:

Column's 'active' possible values:
0-Inactive
1-Active

To get all active members:

SELECT * FROM member WHERE active=1;

You could even make an index for the 'active' column...

[Roboblue]

Bule
I am not sure what you mean. Moving data around easily is what a database is all about.
If the word "move" bothers you, then let's say copy from, paste to, and delete original. That's the way Windows Explorer handles a "move" action.
What I am saying is, when the member becomes inactive, copy his data into an inactive table (or database), then delete him from the active member table. Then when you query the active table(s), you are not querying the data from the non members table.
But, when you do want to report on ALL the members (including the 10 year archive) just include the inactive member table in the query.
And, if that member becomes active again, copy his data back to the active member table, and delete him from the inactive.
These actions are certainly doable in SQLite.

[Dermot]

Bule is right, just flag them as active or inactive and then filter the Select statement. It is much easier to update one field to change a members status than to have to copy the whole record to another table.

Another reason not to move them to another table is if you use auto-increment for the member id field, referential integrity will be lost when you move a member to another table because the member id will be different. Same when you move them back. This means that any records in other tables that reference the member id such as the reminders table will be broken and may even point to a different member.

Keeping them all in one table and just updating one column for their status will make sure that referential integrity is never broken.

[Roboblue]

I guess that's what makes us all different. :D
It would be simple enough to automate a "cleaning" action for database maintenance. Then on a scheduled maintenance day, just pull all expired members out of the active database.
I don't know how many records this database would have, but it seems to me that a government sponsored event that has a requirement to maintain an ex member list for ten years could get quite large, active and inactive.
And, you don't have to use auto increment the user ID. Especially if there is a list already being kept that has to be manually put into a database. The member ID may already be an assigned sequence.
Anyway, there's a lot of things we don't know about this database, so it's a moot point at this time, anyway.

[Jonas DK]

Roboblue I like your Idea about copying and deleting inactive members.

I could have it store the Values in another table, that would be the easy way and the smartest way, because you then dont have that data to filter in the main table, so it would reduse load.

I dont use the ID field to reference anything I use the member CPR field witch is a constant number that will never change It also gives me the abillity to pull automated address change and stuff like that from goverment archives.

So the moving of data is not a problem. I can freely move members from one table to another. Also I can save some space because it is not all the information on a member that they are required to store, only the main info like the CPR, Name adress and sutch.

I don't know what is usually done, as I am not that famillier with the SQLite plugin, Im learning as I go along here.
But it makes much sence to move the data out of the original table and into an archive. Because once a year they have to print a list of inactive members and you could easily implemnt a cleaning rutine at that time so when printing it also deletes record older then 10 years in the archive table.

I think I'm going to look into this one...

Thank you all for the help so fare..

Jonas

[bule]

Roboblue, your idea is okay, but keep in mind this what Dermot said... referential integrity is what relational databases are all about.

You shouldn't worry about SQLite's ability to query through big tables, it is very very fast.