Hey all,
New to access but learning every day. I'm working to merge two access databases so I can retire the old one. I'm currently working on merging multiple fields from one table into one table that will allow one--> many. In the current setup [CFISupp] any particular CFI can only have 1 C152 Check.
Data in separate tables marked with [] 1st Table is [ACAuth] and Second table is [CFISupp]. In [ACAuth] Type Check refers to a specific field in [CFISupp] like 1=C152check, 2=C172check. In [ACAuth] the Date field should be copied from the date fields C152check and C172Check. CFIID is a unique person. Disregard "" in the example, they are only used to explain my meaning, not to indicate I want a string. Example..
[ACAuthID"Auto Number" / Type Check/ Date / CFIID] ................ [CFIID / C152Check / C172 Check]
For Example in [CFISupp] I have
.........................................................................................[200 / (1/1/2012) / (1/15/2012)]
I would like it to transfer to [ACAuth] like
[1"autonumber" / 1"Type, because date of check came from field C152Check" / (1/1/2012) "date from [CFISupp]![C152Check]" / 200 "CFIID=200"
[2 "autonumber" / 2"Type, because date of check came from field 172check" / (1/15/2012) "Date from [CFISupp]![C172Check]" / 200 "CFIID = 200"
or numbers only
[1 / 1 / (1/1/2012) / 200]
[2 / 2 / (1/15/2012) / 200]
With this format I can allow the user to input a second C152 Check, making the table look like
[1 / 1 / (1/1/2012) / 200]
[2 / 2 / (1/15/2012) / 200]
[3 / 1 / (1/20/2012) / 200]
I've tried some basic update queries to accomplish what I am trying and I can get the C152 check data over into the [ACAuth] table but when I try to update C172 check data it over writes the 152 date with the 172 date for the same CFIID. I want it to add a new record with the same CFIID and the 172 date.
Help is appreciated! Hopefully my description makes some sense.
Chris
New to access but learning every day. I'm working to merge two access databases so I can retire the old one. I'm currently working on merging multiple fields from one table into one table that will allow one--> many. In the current setup [CFISupp] any particular CFI can only have 1 C152 Check.
Data in separate tables marked with [] 1st Table is [ACAuth] and Second table is [CFISupp]. In [ACAuth] Type Check refers to a specific field in [CFISupp] like 1=C152check, 2=C172check. In [ACAuth] the Date field should be copied from the date fields C152check and C172Check. CFIID is a unique person. Disregard "" in the example, they are only used to explain my meaning, not to indicate I want a string. Example..
[ACAuthID"Auto Number" / Type Check/ Date / CFIID] ................ [CFIID / C152Check / C172 Check]
For Example in [CFISupp] I have
.........................................................................................[200 / (1/1/2012) / (1/15/2012)]
I would like it to transfer to [ACAuth] like
[1"autonumber" / 1"Type, because date of check came from field C152Check" / (1/1/2012) "date from [CFISupp]![C152Check]" / 200 "CFIID=200"
[2 "autonumber" / 2"Type, because date of check came from field 172check" / (1/15/2012) "Date from [CFISupp]![C172Check]" / 200 "CFIID = 200"
or numbers only
[1 / 1 / (1/1/2012) / 200]
[2 / 2 / (1/15/2012) / 200]
With this format I can allow the user to input a second C152 Check, making the table look like
[1 / 1 / (1/1/2012) / 200]
[2 / 2 / (1/15/2012) / 200]
[3 / 1 / (1/20/2012) / 200]
I've tried some basic update queries to accomplish what I am trying and I can get the C152 check data over into the [ACAuth] table but when I try to update C172 check data it over writes the 152 date with the 172 date for the same CFIID. I want it to add a new record with the same CFIID and the 172 date.
Help is appreciated! Hopefully my description makes some sense.
Chris
