Solved: Access - import table - add date to table

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

chudok01

Thread Starter
Joined
Sep 16, 2010
Messages
204
I am starting this Access database from scratch. I have to import/upload a sales file weekly. I will be adding this file to a main sales data table. The issue I have is that this file does not have any dates on it. I need to be able to add a start and end date to the table.

Example fields that I would need/have

Need:
start date
end date

Have:
Item #
Store #
Sales Units
Sales Dollars

What is the best way to handle this? I have an idea - just not sure what order I should put my steps for importing the file, asking for the date range, adding the date to the table, etc....
 

chudok01

Thread Starter
Joined
Sep 16, 2010
Messages
204
They would be weekly from Saturday - Friday

May 5 - May 11
May 12 - May 18


etc....
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I would have a form for selecting the dates that match the data, you could have a table with the dates pre-entered.
You could then store the IDkey field from the dates table in the imported data table using the Dlookup function in the query that you use to append the data to the table.
The external data would be imported in to a "Temporary" table and appended to the real table using the query that also gets the dates Key field.
The dates in the dates table can be created using VBA to create a years worth at a time.
 

chudok01

Thread Starter
Joined
Sep 16, 2010
Messages
204
I am just now starting on this project again..... I and I am re-reading the thread. I think there might be some confusion..... when I said I know the dates - there is nothing in the file that tells me the dates. Someone has to manually grab data from a date range they enter and product a spreadsheet from the data. I only know that the date ranges are supposed to be Saturday - Friday.

So I really don't understand how to do this......
 

OBP

Joined
Mar 8, 2005
Messages
19,895
You create a table with a key field and the date ranges in it, KeyID field + 2 fields Start Date and End Date.
The user would select the Start date in a Combo, the key field for that record would be stored on the form.
The Append query would have the Imported data table + the Dates table. The keyID field of the dates table would have the form's selection as it's Criteria.
 

chudok01

Thread Starter
Joined
Sep 16, 2010
Messages
204
My combo shows both my begin and end date when I click the drop down. But once i select the range I want only the begin date shows in the combo window. How do I get both to show up after I select the range? (i have 2 different fields - begin date and end date. my begin date is my primary key)
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Create a combined field column using
[Field1] & [" to " & [Field2]
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top