Unanswered: Need help with inserting records, combining tables
Hi I have two tables in Access, one has dates in it, every record is unique, it has a field for the date name example Monday, date example 10th, year and month field. Then I have another table that has 500 records, each record has very similiar fields so the primary key would have to be the whole row being that the whole record would be unique but the fields would not be. Being that I have row numbers,section, place, description, the only way the record is unique is by saying row and section and place so those could be the keys but that is not my concern. What exactly I need to do is
I need to apply the dates to every item in the table that has 500 items. So I would need 500 items with date one, then create 500 items with date two and so on, for the whole year, I need every row in the item table to have each date applied to it, but I need it to be exactly the way I have described. Any ideas on how to do this? I have to actually duplicate every record in the item table 500 times for each date and have each day applied to every record of those 500, ending up with over 180,000 records I know but this is what I need. Thank you
dbuser123456789, What you want to do is what you don't want to do most of the time. It is basically a join without a join predicate (a.col = b.col). Try
SELECT A.COL1, A.COL2, A.COL3, A.COL4
, B.COL1, B.COL2, B.COL3, etc.
FROM date-table A
, table-w-data B
(SELECT A.*, B.* may also work)
This will select row 1 from the date table and return 500 rows from the table with data (all having the same date value). Then row 2 from the date table and all 500 rows from the table with data, Then row 3, etc. until you get to the end of the rows in the table. You will end up with 'Date table rows * Table with data rows = total rows returned' rows of data.