Missing Auto Population in three tables

[Paul Banfe]

Dear Sir,
I’m trying to write a database for an American Legion Post. To Track Members, and annual dues collection,
I wrote three tables.
1.Legion Master Table:, with Names, addresses, contact information etc.
a.KKEY is Primary Key
2.Legion Year Table: Post Address and year of operation, It allows the user to add a new year to the list.
a.YKEY is the primary Key
3.Finance Table: Record the receipt of Annual Dues from Members -date of receipt, amount paid Check number or cash.
a.I’d like the KKey, first and last name fields auto populated.
I wrote a query using the KKey and first name and no matter the relationship I express in the query, I can’t get the KKEY and first and last name to auto populate. I used K-KEY into FK_KKEY in the Finance table and YKEY into FK_YKEY. Also. in the finance table. I use a drop down to put in the correct year of the collection
What Am I doing wrong to get these three fields to auto populate from the Master database?
Help!!!

[NeoPa]

You should only ever populate data for Fields in Queries (See Database Normalisation and Table Structures for tips about understanding basic database work).

More than that is very difficult to help with at this point. You say a lot - but without really conveying much understanding of what you've tried & what isn't working.

You do need to remember that we only know what you tell us, and even that only if your explanation is clear. You lost me before the half-way point.

[lewish95]

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

Create a table that only holds incrementing numbers - easy to do using an auto_increment:

DROP TABLE IF EXISTS `example`.`numbers`;
CREATE TABLE `example`.`numbers` (
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Populate the table using:

INSERT INTO `example`.`numbers`
( `id` )
VALUES
( NULL )
...for as many values as you need.

Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-06-06" and "2010-06-14" with your respective start and end dates (but use the same format, YYYY-MM-DD) -

SELECT `x`.*
FROM (SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY)
FROM `numbers` `n`
WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14' ) x
LEFT JOIN onto your table of data based on the time portion:

SELECT `x`.`ts` AS `timestamp`,
COALESCE(`y`.`score`, 0) AS `cnt`
FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%m/%d/%Y') AS `ts`
FROM `numbers` `n`
WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`, '%d.%m.%Y') = `x`.`ts`
If you want to maintain the date format, use the DATE_FORMAT function:

DATE_FORMAT(`x`.`ts`, '%d.%m.%Y') AS `timestamp`